nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to Fetch more than 1000 records with BIP or OTBI report

Sudheer YN

Summary:

Fetch more than 1000 records with BIP or OTBI report.

Content (required):

Hi,

How to fetch more than 1000 records with BIP or OTBI report.

I tried with Parameter as Free Text field and provided values with comma separated. I am able to run the report with 1000 records/values and not more than that. At times, we get RECALLs for over 1500 products and we need to run the report for all the products.

Please advice.

Version (include the version you are using, if applicable):

23A

Code Snippet (add any code snippets that support your topic, if applicable):

Error:

ORA-01795: maximum number of expressions in a list is 1000

Find more posts tagged with

Reporting

BI Publisher

OTBI

Report Output

Accepted answers

Bhaskar Konar

Hi @Sudheer YN,

Oracle allows up to 1,000 IN list values in a SQL statement.

It can be achieved with some work around. If you are using OTBI Report you can do an Union Report or use those conditions as OR filter to accommodate more than 1000 entries.

If you are using BIP then you can use the OR condition as mentioned below as a work around.

select *
 from table
where val in (1,2,3,...)
or
val in (7,8,9,....)

Hope this help.

Cheers,

Rich Merkel

I'm not sure if this still works, but I had a note from a colleague a long while back specific to BI Reports

Here is the workaround to circumvent count restriction within SQL IN clause. For some reason if you put 0 on both sides, it works.

Select * from xx_table

Where (col_name,0) IN

(

('abc',0),

(‘abc1’,0),

------ any number of values

(‘zzz’,0)

);

The above is an example with alphanumeric values – which requires sting being in quotes. For numeric value, do not include quotes.

All comments

Bhaskar Konar

Hi @Sudheer YN,

Can you please try the following logic:

Create a Report to identify all the Products where you can use Product Family / Any Range search to identify all Products that will be required to be filtered on main report. It might be returning more than 1000 rows.
Now develop the main report where use the Report in Step1 as a filter in the sub-query.

Hope this help.

Cheers,

Sudheer YN

Hi @Bhaskar Konar , So we have to restrict it with Filter, but cannot use more than 1000 values in the conditions right. I mainly want to know its possible or not. I understand we can do it with additional filters or restrict with additional parameters.

Bhaskar Konar

Hi @Sudheer YN,

Oracle allows up to 1,000 IN list values in a SQL statement.

It can be achieved with some work around. If you are using OTBI Report you can do an Union Report or use those conditions as OR filter to accommodate more than 1000 entries.

If you are using BIP then you can use the OR condition as mentioned below as a work around.

select *
 from table
where val in (1,2,3,...)
or
val in (7,8,9,....)

Hope this help.

Cheers,

Sudheer YN

Hi @Bhaskar Konar

Thank you. I am looking for below confirmation on the limitation.

"Oracle allows up to 1,000 IN list values in a SQL statement."

Gaurav Bharadwaj-Oracle

https://community.oracle.com/customerconnect/discussion/comment/754393#Comment_754393

Yes, that is correct this is a SQL limitation. Please use filters instead or the approach that Bhaskar suggested to workaround this limitation.

Thanks

Rich Merkel

I'm not sure if this still works, but I had a note from a colleague a long while back specific to BI Reports

Here is the workaround to circumvent count restriction within SQL IN clause. For some reason if you put 0 on both sides, it works.

Select * from xx_table

Where (col_name,0) IN

(

('abc',0),

(‘abc1’,0),

------ any number of values

(‘zzz’,0)

);

The above is an example with alphanumeric values – which requires sting being in quotes. For numeric value, do not include quotes.

Boying

https://community.oracle.com/products/oracleanalytics/discussion/comment/45093#Comment_45093

Thank you @Rich Merkel. This workaround worked. I have 3,877 records and was able to include in IN statement.

Sudheer YN

Hi @Rich Merkel ,

Thank you. but this works only when we are querying in SQL tools. But we cannot use this logic in Report Parameters etc right.

Regards,

Sudheer.