Categories
- All Categories
- 104 Oracle Analytics News
- 10 Oracle Analytics Videos
- 14.3K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 58 Oracle Analytics Trainings
- 60 Oracle Analytics Data Visualizations Gallery
- 5 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
How to Fetch more than 1000 records with BIP or OTBI report

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
Best Answers
-
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,
0 -
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.
1
Answers
-
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,
0 -
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.
0 -
Thank you. I am looking for below confirmation on the limitation.
"Oracle allows up to 1,000 IN list values in a SQL statement."
0 -
Yes, that is correct this is a SQL limitation. Please use filters instead or the approach that Bhaskar suggested to workaround this limitation.
Thanks
0 -
Thank you @Rich Merkel. This workaround worked. I have 3,877 records and was able to include in IN statement.
1 -
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.
1