Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE - Subquery contains too many values for the IN predicate

Received Response
423
Views
3
Comments
3713479
3713479 Rank 4 - Community Specialist
edited Aug 13, 2024 5:25PM in Oracle Analytics Cloud and Server

Can anyone suggest a reason why this error only occurs when the report is run from within a Dashboard ?

When run outside the Dashboard, no error is triggered. 

The report filters are based on prior reports.  The prior reports trigger filters on other reports which increase the total rows queried.

Online posts suggest increasing the MAX_EXPANDED_SUBQUERY_PREDICATES from 8K to a higher value such as 15 or 25K.

Thanks for any insights on this issue. 

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Are you really talking about BI Publisher reports? Or is it analysis (the Answers analysis you put in dashboards) ?

    You don't really provide much info, so it's all about guessing ...

    Did you look at the generated query?

    Most databases have limits on the number of elements in a IN clause, even Oracle is limited to about 1000 or so by default. Sure it can be changed on the database level but the question is more about if you really want it.

    Who is returning the error? OBIEE or the database?

    Post more details and something more concrete can be provided ....

  • 3713479
    3713479 Rank 4 - Community Specialist

    This is not BI publisher, but analysis that I placed in the Dashboard.

    Overnight tonight,(6-12), the DBA is going to bump the MAX_EXPANDED_SUBQUERY_PREDICATES TO 25K from the present 8K & recycle the DB instance.

    I assume that the database is triggering the exception & not OBI, but I am offering the error text below....

    Odbc driver returned an error (SQLExecDirectW

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

    State: HY000.  Code: 10058.  [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed:  [nQSError: 42029] Subquery contains too many values for the IN predicate.

  • 3713479
    3713479 Rank 4 - Community Specialist

    Updating the value for the parameter MAX_EXPANDED_SUBQUERY_PREDICATES to 25K from 8K resolved this issue.

    Thanks to Gianni for offering his input.