Skip to Content

Results from another query limited to 1000 rows

I have occasionally used the "Results from another query" as an operand in a query filter.

Unfortunately, if my base query has too many values I get this error.

But how many is "too many"? So I did a little testing and set my "Max rows retrieved" in the Query Properties to a variety of numbers and got to the point where it works for 1,000, but not 1,001.

So is this some sort of global BI limitation? Is it limited at the universe somehow? Or maybe the underlying database may be the issue? For this example I am hitting the database is Oracle 11g Enterprise Edition Release 11.2.0.3.0.

I am curious where the bottleneck is on this situation.

Thanks,

Noel

pastedImage_4.png (56.7 kB)
pastedImage_5.png (10.7 kB)
pastedImage_7.png (30.6 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    Posted on Sep 25, 2013 at 05:42 PM

    I did another example on a universe that is hitting a SQL Server 2008 database. I bumped the "Max rows retrieved" up to 10,000 with no problem. I did find that WebI will not allow me to enter a value greater than 10,000. No errors, it just won't do it.

    Noel

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Noel,

      You can pass 32000 bytes along with IN ( ... ) operator in where clause. And this no. varies for each database. That's why some time you are able to pass 10000 records and some time only 3000 records using Query on Query.

      In Query on Query, if you see SQL of parent query; you will notice that all the values from filter query are listed with IN ( ... ) operator. It become very big bloated SQL.

      This is not a BI limitation. This is database limitation. Job of BI tool is to prepare a good query with proper syntax. Once query is prepared, its being submitted to database for execution. So if database finds that size of IN ( ... ) exceeded so it throws error.

      I hope this clarifies your doubt.

  • author's profile photo Former Member
    Former Member
    Posted on Sep 26, 2013 at 07:52 AM

    Hi Noel,

    To remove any kind of restriction from BO perspective, you can assign value as -1 to

    MAX_INLIST_VALUES parameter in the universe.

    As correctly mentioned by Hardik, the maximum value will depend on Database which you are using as the limitation varies from database to database.

    If you check explanation on above parameter in Business Objects Designer guide, you will find that they mentioned as:

    MAX_INLIST_VALUES:

    MAX_INLIST_VALUES = [0-99]

    Values Integer: min -1, max depends on DB

    Default -1

    i.e. max value depends on database.

    Check page number 97 in below guide:

    http://help.sap.com/businessobject/product_guides/boexir31/en/xi3-1_designer_en.pdf

    Regards,

    Yuvraj

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.