Is a subquery in a BO report limited to a max number of records???

[Former Member]

Here's my problem:

I recieved an excel sheet with 700 records of customers from a client who wants me to to create a report with specific data for these customers in my Business Objects universe (BO6.5 on SQL Server).

So I created a dataprovider with query 1, i.e. the requested data of customers. Then I created a second dataprovider, query 2, based on 'personal files', i.e. the excel sheet. In query 1 I added to the conditions that each customer should be in (sub)query 2 (CustomerId In list of the query result ('query2.CustomerId').

the syntax I have used for this seems OK.

However, I recieve the following error: "Too many selected values (LOV0001)". I know this error has to do with parameter MAX_INLIST_VALUES, which is limited by default to 99 and can be extended to 256 max. But I thought it refers to the max number of items in lists of values.

When I limit the number of records in the excel sheet to 99 the result is perfect (proof that I got the syntax right!). I can upgrade the parameter to 256, and can split the excel sheet into three, but that will not be useful when next time my client sends me 10.000 customer records.

Can I make reports in BO which use subqueries that result in more than 256 records at all? (hardly imaginable).

What is the best way to do this?

Thanks in advance!

[Former Member · Accepted solution]

Hi Marc,

Could you please test the issue by keeping the MAX_INLIST_VALUES

To -1?

Regards,

Sarbhjeet Kaur

[Former Member · Accepted solution]

Hello Sarbhjeet,

Thank you very much for your quick response.

It works, so my problem is now solved.

Sincerely,

Marc

[Former Member]

Hi,

I am neu in this forum but I have the same problem.Is it possible to solve this Problem?

Can I upgrade this Parameter MAX_INLIST_VALUES, for example to 500.000 (for 500.000 customer records)? I can split excel sheet into 50 but it is not so efficient.

So who can help me?

Thanks in advance!

Isabell

[Former Member]

Hi Switas,

This depends upon the database you are using.

Could you please test the issue by keeping the max_inlist_values=-1.

Regards,

Sarbhjeet Kaur

[former_member212749]

Hi Sabarjeet ,

Can you tell me the path of the .prm file ?

Regards

Prashant

[Former Member]

Hi Prashant,

could you please open the corresponding universe on which the report is based and go to file->parameters->test the connection->click on details button.

Scroll down you will get the .PRM file path open the corresponding .prm file path and do the required changes.

I hope this clarifies better!

Regards,

Sarbhjeet Kaur

[Former Member]

Hi Lucas,

May I know how many lovs you are trying to pull out on the BO level.

About 20 000

Add the MAX_INLIST parameter in .PRM file and set it's value to 256.

I set in files sqlsrv.prm (odbc and oledb folder), odbc.prm like this

<Parameter Name="MAX_INLIST">256</Parameter>

Also test the issue by setting the value under designer parameters to 999.

I set MAX_INLIST_VALUES to 999

Regards,

Sarbhjeet Kaur

I have the same problem: Too many values (LOV0001).

Edited by: Lucas on Dec 3, 2008 9:35 AM

[Former Member]

Hi Lucas,

Following is the information regarding the issue you are getting and might help you to resolve the issue.

ADAPT00519195- Too many selected values (LOV0001) - Select Query Result operand

For XIR2 Fixed Details-Rejected as this is by design

I have found that this is a limitation by design and when the values exceed 18000 we get this error in BO.

There is no fix for this issue, as itu2019s by design. The product always behaved in this manner.

Also an ER (ADAPT00754295) for this issue has already been raised.

Unfortunately, we cannot confirm if and when this Enhancement Request will be taken on by the developers.

A dedicated team reviews all ERs on a regular basis for technical and commercial feasibility and whether or not the functionality is consistent with our product direction. Unfortunately we cannot presently advise on a timeframe for the inclusion of any ER to our product suite.

The product group will then review the request and determine whether or not the functionality/feature will be included in a future release.

Currently I can only suggest that you check the release notes in the ReadMe documents of future service packs, as it will be listed there once the ER has been included

The only workaround which I can suggest for now is:

Workaround 1:

Test the issue by keep the value of MAX_Inlist_values parameter to 256 on designer level.

Workaround 2:

The best solution is to combine 'n' queries via a UNION. You should first highlight the first 99 or so entries from the LOV list box and then combine this query with a second one that selects the remaining LOV choices.

Using UNION between queries; which is the only possible workaround

Please do let me know if you have any queries related to the same.

Regards,

Sarbhjeet Kaur

[Former Member]

Hi,

I have a prompt which I need to pass more than 2000 values using the operator Inlist, but I get the error "too many values". My database is oracle 9 and I have MAX_Inlist_values parameter set to -1 but I still get same error. Do you know If Oracle has this problem too? I have BOE XI 3.1.

Thanks,

Sandra.

[Former Member]

Hi Sandra,

If by setting the MAX_INLIST_VALUES to -1 doesn't works then it is a database limitation due to which you get this error message and as you are using the ORACLE 9 version supports not more than 999 inlist values.

You can use union,intersection or subquery to make it work.

I hope this will help you.

Regards,

Sarbhjeet Kaur

[Former Member]

I changed MAX_INLIST parameter to -1 and I've the same problem. I used SQL Server 2005.

Any ideas why doesn't work?

[Former Member]

Hi Lucas,

May I know how many lovs you are trying to pull out on the BO level.

Add the MAX_INLIST parameter in .PRM file and set it's value to 256.

Also test the issue by setting the value under designer parameters to 999.

Regards,

Sarbhjeet Kaur