Results from another query limited to 1000 rows

[nscheaffer]

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

[nscheaffer · Accepted solution]

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

[Former Member · Accepted solution]

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.

[Former Member]

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

[nscheaffer]

I am not in control of the universes so I cannot add universe parameters.  Thank you for the information.

Noel