Last updated on SEPTEMBER 24, 2014
Applies to:Business Intelligence Server Enterprise Edition - Version 188.8.131.52.1 and later
Information in this document applies to any platform.
You have 2 parent child hierarchies defined in 2 separate subject areas,
The parent child hierarchy in subject area 1 works fine and gives results fast.
However when running a report based off the 2nd parent child hierarchy, the following error is thrown
"Subquery contains too many values for the IN predicate."
When the setting for MAX_EXPANDED_SUBQUERY_PREDICATES = 4000000 is set, then the error no longer happens , however report takes a very long time to finish,
The main issue is with the type of SQL generated between the working and non-working hierarchies:
- In the working hierarchy sql, the IN clause as a subquery select to retrieve the child nodes.
- In the non-working SQL generated, the query first issues sql to get the values of leaf and then passes the values in the IN clause in the subsequent query. Since it has to pass so many values in clause is why the error occurs and circumventing the error by altering the nqsconfig setting leads to performance issue.
The requirement is that the second parent child hierarchy should also generate a SQL like the 1st hieararchy where it uses subquery in IN clause.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms