Error in SQL generation - Is this an Universe issue??

building semantic layer
1

Hi,

I am developing few reports and have couple of summary tables as basis for those. We have an existing universe and new classes/objects have been added for these tables/reports. Now when I am generating the Report (Query Panel) I am facing a strange issue -

  1. Refer the Conditions Panel in any of the attached screenshots. The Conditions coded here should appear in proper format along with proper backets (parenthesis) for various ‘AND’ and ‘OR’ operators.
  2. Refer the SQL Viewer. This shows how the SQL is being generated by BO tool.
    In ‘Problem Scenario’, refer ‘where’ condition, there are no brackets in the generated SQL. And this is a problem. If the brackets(parenthesis) are not present in the generated SQL, that means it will bring wrong data in the report as wrong combination of filters will be applied while querying database.

Attached is the screenshot of problem and expected scenario.

Initially, I thought this is a problem with our universe. But I was able to replicate the problem with eFashion (Sample universe provided during installation) universe also. :frowning:

Any pointers? Solutions? Workarounds??

Thanks,
smartvnr
ProblemSQL_Generation_BO.jpg
ExpectedSQL_Generation_BO.jpg

smartvnr :india: (BOB member since 2003-09-24)

2

There’s nothing wrong with that SQL, did you actually try to run it? :wink: It works fine.

AND takes priority over OR. If you do this:

((1 AND 2) OR (3 AND 4))

it is exactly (in a logical sense) the same as

1 AND 2 OR 3 AND 4

It will return exactly the same results. Now if you need to do this:

((1 OR 2) AND (3 OR 4))

then you must have parenthesis in order to give the OR a priority over AND.

Try it and see, you will see that it works.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Hi Dave,
Thanks for your response. Attached screenshots were just samples to explain what is happening… The condition I am trying to incorporate is much complex than this. It is somewhat like -
Where
C1
AND ( ( C2 AND C3 )
OR ( C4 AND C5 AND C6 AND C7 )
OR ( C8 AND ( C9 AND C10 AND C11 AND C12 ) )
OR ( C13 AND C14 ) )

*C1 to C14 represent different conditions.

Also, I verified by running SQL with and without brackets, number of rows returned differ.

  • Vnr

smartvnr :india: (BOB member since 2003-09-24)