My Oracle Support Banner

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 11.1.1.3.0 [1905] and later
Business Intelligence Server Enterprise Edition - Version 11.1.1.3.0 [1905] and later
Oracle Exalytics Software - Version 1.0.0.0.0 and later
Information in this document applies to any platform.

Symptoms

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)"

Changes

Upgraded OBIEE from 10.1.3.4.1 to OBIEE 11.1.1.5.0 or 11.1.1.6.

Cause

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
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.