Oracle Transactional Business Intelligence

Products Banner

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

Accepted answer
697
Views
8
Comments

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

  • Bhaskar Konar
    Bhaskar Konar ✭✭✭✭✭
    Answer ✓

    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
    Rich Merkel ✭✭✭✭
    Answer ✓

    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.

Answers