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

Max W

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.

SteveF-Oracle

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.

User_HCG55

Hi,

The best workaround I used is disabling 'Can select all' and enabling 'Multiple selection'. This way we are giving the users to run the report for a maximum of 999 values at once. Please refer to the attached screenshot.

Regards

Jehosheba Amruthaluri

LOV selection issue in BIP report.png

Gianni Ceresa

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

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.

Gianni Ceresa

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.

GHT

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

Gianni Ceresa

https://community.oracle.com/products/oracleanalytics/discussion/comment/51500#Comment_51500

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

@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.

Gianni Ceresa

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 (....) .

Avishan

@SteveF-Oracle This worked, Thank you.