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

Sudheer YN · March 2023

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

Bhaskar Konar · March 2023

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 · March 2023

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.

Bhaskar Konar · March 2023

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 · March 2023

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.