cancel
Showing results for 
Search instead for 
Did you mean: 

Why does the Semantic Layer produce newly unwanted subqueries?

Former Member
0 Kudos

Why does the Semantic Layer produce Unwanted subqueries, and previously working subqueries now produce insane SQL?

I have a Universe that has been developed over two years. Until recently I was able to produce rational SQL using prompts in the universe within Crystal Reports for Enterprise (all version 4.1). Using the semantic layer in Business Objects Edge 4.1 sp4, I'm seeing previously working reports failing now with strange SQL. See this example:

The results are pretty simple, and there are three contexts in the universe for reaching a Row-Level-Security table by one of multiple means set forth during query creation. That's all working, but this unnecessary concatenation of all fields to join to a subquery producing all of the columns is clearly unwanted and unwarranted.

Further, existing previously working subquery-based reports are now producing SQL that can never work, such as when joining a table onto itself using its primary key, but the outside query is using this concatenation instead of what is requested:

This has made our entire reporting platform viability suspect. Existing reports with filters defined in the query filters section (such as {Result Object} like '%some Value%') is generating this crazy subquery condition instead of a normal in-line where condition as it did when it was developed.

In the second example above, You can see that {Ticket Link Value} (the primary key) is equal to {Ticket Link Value} (the same object) in the subquery, but now the where condition in the outer query is concatenating all of the columns in the outer query with Backslashes and using "IN" ( the primary key specified in the subquery).
Reverting to an earlier universe makes this problem go away at the expense of all new objects and reports missing. That is not an acceptable solution either.

This is urgent to get this resolved.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member187093
Participant
0 Kudos

I would like share our experience, We had same kind of issue with oracle backend. We found that the reverse table weight parameter was changed when we did the upgrade hence changed the parameters at oracle.prm file. Maybe check the prm file based on your backend. Hope this helps!