Oracle Analytics Cloud and Server

Products Banner

Is there any way to get around - ORA-01795 : Maximum number of expressions in a list is 1000

Received Response
3514
Views
9
Comments

I have created a secondment report, and the business want a parameter in Oracle BIP with Locations. However we have over 1000 active locations.


SELECT location_name 

FROM hr_locations_all_x 

WHERE ACTIVE_STATUS = 'A'


Is the LOV used, but this causes the error message. If I use FETCH FIRST 999 ROWS ONLY, this excludes data from the report.


Is there a way for me to get all active locations in the parameter?


Thank you for your help.

Tagged:

Answers

  • In SQL you aren't supposed to write an infinite number of values in a IN condition. For that need there are joins: just join together your tables and they will automatically filter the values just like the IN would do, but it's a lot better for performance (because that's the way a database is meant to work).

  • Max W
    Max W ✭✭✭

    Thanks for replying @Gianni Ceresa . Do you have an example of this by any chance, can't wrap my head around your comment as I didn't think the location table is infinite. ?

    We have 1090 locations active in the parameter query for the LOV.

    To get locations for employees in the data set I use joins:

    left join hr_locations_all_x   loc

    ON pasg.location_id = loc.location_id

    AND sysdate between loc.effective_start_date and loc.effective_end_date

    (with the assignment table as the join).

    Thank you for your help.

  • Well, it obviously depends on your whole query.

    If you have something like this:

    SELECT something
    FROM a_table
    WHERE location IN (
     SELECT location_name
     FROM hr_locations_all_x
     WHERE active_status = 'A'
    )
    

    You should look for something like this instead:

    SELECT a.something
    FROM a_table a,
    hr_locations_all_x l
    WHERE a.location = l.location_name
    AND l.active_status = 'A'
    

    The IN condition disappear and the inner join will do the same job.

  • Any suggestions if there is the same error because of a parameter?

  • If you are passing a parameter to a query that has more than 1000 values, you are doing it wrong somewhere.

    What is the reason to have more than 1000 values in a parameter?

  • Max W
    Max W ✭✭✭

    @Gianni Ceresa our business has over 1000+ locations - we wanted to have a parameter that showed all of this.


    This is allowed in OTBI, for departments which is way over 1000 and locations for example, but when we need to move reports to BIP this is a limitation for our customers. Thanks.

  • Ok, so good to know that your issue is actually with BIP.

    Is your use case that you want to select 1495 locations of your total 1500 locations?

    Because then you should switch to exclude the 5.

    Or you simply wants them all, then you should skip filtering it fully.

    All in all: the ORA-01795 is a database error. It isn't BIP or Oracle Analytics not supporting 1000+ parameters, it's the database forbidding it in your query.

    You can change the database to allow more than 1000 (quite sure it is possible), but you shouldn't do it and if you ask for it your DBA will not really say "yes" (he will maybe be even less polite than that).

    There are plenty of ways to avoid having to pass 1000+ values in IN condition in a query, you have to adopt one of those, change your query to not be a IN (....) .

  • To add to the response for BIP. Agree with Gianni, it comes down to the modeling.

    As mentioned, this is a driver cursor limit (999).

    One potential workaround to pass along.

    In the Datamodel screen, there is an option of parameter, either select all 'All values' passed or 'Null values' passed. You can try/test the Null option.

    The 'All values' option, it will try to query the database in one shot for more than 1000 values. The Oracle Database doesn't allow querying more than 999 records at a time.

    However, if you select the 'Null values' option, all of the values will not be queried in one shot, instead, the user can select any number of values up to 999 and view the report.

    Hopefully, this old thread can be closed.