OBIEE 11g: Error: Upgraded 10g Report Filtering on "Use Request Results" Fails With Error Codes: "OPR40NWY:U9IM8TAC:OI2DL65P...Query failed: [nQSError: 42029] Subquery contains too many values for the IN predicate. (HY000)"
(Doc ID 1339708.1)
Last updated on FEBRUARY 19, 2020
Applies to:Business Intelligence Suite Enterprise Edition - Version 188.8.131.52.0  and later
Business Intelligence Server Enterprise Edition - Version 184.108.40.206.0  and later
Oracle Exalytics Software - Version 220.127.116.11.0 and later
Information in this document applies to any platform.
After upgrading to Oracle Business Intelligence Enterprise Edition (OBIEE) 11g, you get an error when using a filter that is based on "use request results." In OBIEE 10g, the first query would run and passed the "query results" to the filter as a sub-query. Essentially, this created one query with an embedded sub-query and the desired and expected results were returned.
In OBIEE 11g, an error is encountered when running the request and the queries are being executed as two separate queries where OBIEE plugs the results of the first request/analysis as predicate values into the second request/analysis.
The resultant SQL includes a where clause with a NOT IN operator and the error that occurs is:
"View Display Error: Odbc driver returned an error (SQLExecDirectW)
Error Details: Error Codes: OPR40NWY:U9IM8TAC:OI2DL65P
....Query failed: [nQSError: 42029] Subquery contains too many values for the IN predicate. (HY000)"
Upgraded OBIEE from 10.1.3.4.1 to OBIEE 18.104.22.168.0 or 22.214.171.124.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document