External List of Values Question

building reports desktopintelligence
1

Can anyone confirm the following:
I am using a personal dataset (Excel Spreadsheet) to provide a list of values for a select query. The query will provide data for selected rows based on the key value contained in the spreadsheet. I believe, but cannot confirm, that the list that is used from this personal data set is limited to 255 entries. I need to query about 5000 values. If the limit is 255 I will need to provide multiple spreadsheets but would prefer not to split the spreadsheet into a series of small spreadsheets. Thanks in advance.

Listserv Archives (BOB member since 2002-06-25)

2

Doug,
There are actually two limits. One limit is the size of the SQL statement. I think this depends on your middleware/DBMS. The other limit is the number of items you can select out of a personal list of values. I don’t know why but the limit we run into is 99. If you end up with a different number let me know.

Can anyone confirm the following:
I am using a personal dataset (Excel Spreadsheet) to provide a list of values for a select query. The query will provide data for selected rows based on the key value contained in the spreadsheet. I believe, but cannot confirm, that the list that is used from this personal data set is limited to 255 entries. I need to query about 5000 values. If the limit is 255 I will need to provide multiple spreadsheets but would
prefer not to split the spreadsheet into a series of small spreadsheets.
Thanks in advance.

Steve Money
Southwestern Bell
SM5064@sbc.com

Listserv Archives (BOB member since 2002-06-25)

3

In a message dated 98-06-09 10:28:18 EDT, you write:

I am using a personal dataset (Excel Spreadsheet) to provide a list of
values for a select query. The query will provide data for selected rows based on the key value contained in the spreadsheet. I believe, but cannot confirm, that the list that is used from this personal data set is limited to 255 entries. I need to query about 5000 values.

5,000 values?

Doug, I would imagine that there is a more effective way to identify your criteria than having an “In List” with 5,000 entries in it. Even if it were possible to pass a list of that size to the database, I suspect that the execution time would be terrible.

Is it possible to identify these entries using a series of ranges? In other words, if you are looking for items from 1000-1250, then 1270-1300, then 1305-1500, then… (you get the idea) a series of BETWEEN operations combined with an OR will not only be more feasible but much more efficient when processed by the database engine. Something like:

code between 1000 and 1250
0r code between 1270 and 1300
or code between 1305 and 1500

Obviously if your individual entries cannot be grouped in ranges, then this is not a valid solution.

Can the rows be identified with an additional table in the database? In this case, lets imagine that your list of 5,000 entries is a list of customer ID’s. You have 1,000,000 customers, so this list represents a statistical sample. By building a table called SAMPLE_CUST with a structure like:

customer_id number(10)
sample_code character(5)

… then you could create entries in the table in the database and identify them by sample_code. Your list of 5,000 customers could be called SAMP1, while another set of customers for some other project could be called SAMP2, and so on. This table would be joined to the regular customer table via the customer_id field, which should be efficient because you would (should!) have a unique index on the customer.customer_id field.

To create your query, then, you would build a condition on the Sample Code object with an Equal To ‘SAMP1’ clause. Instead of a massive “In List” you have a straight forward (and much more efficient) join.

In other words, I am having a hard time imagining a scenario where I would want to have a conditional list with 5,000 values. Perhaps you can share some more details? Or perhaps one of these ideas will help as is.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

See you in Orlando in '98!

Listserv Archives (BOB member since 2002-06-25)

4

The Limit in the List of Values varies between versions of Business Objects.
I believe the inlist limit is 255 or 256 in BO 4.0.5.x and 99 in 4.1. The Change seemed to stem from a differing number of values that were acceptable for each database. To simplify BO seemed to simply use the lowest common denominator… in this case 99.

Listserv Archives (BOB member since 2002-06-25)

5

We are currently migrating from B.O. v3.1 to v4.1 and noticed a difference in use of the external list of values. In v3.1 we were limited by Oracle to 255 but not by B.O… In v4.1 we became limited to 99 values by B.O… We found that we could get around this by changing from the Tabular view to the Hierarchical view upon entry into the List of Values window and before any values have been chosen. We were then able to select 5000+ values (although still limited by Oracle) and the results from the query seemed fine. Note, after the initial setting to Hierarchical, switching between Tabular and Hierarchical will return a message of “Too many selected values”, in which case you will need to close the List of Values window and reenter.

Listserv Archives (BOB member since 2002-06-25)