Impromptu Pick Lists 1

[rxdatadude]

As a limitation of Oracle I can only use 1,000 items in a picklist/in statement. Can I create multiple picklists from the same underlying file? That is, if I have a list of 20,000 members in a text file, can I create picklists as follows without having to create 20 text files:
pick1: use rows 1-1,000
pick2: use rows 1,001-2,000
......
pick20: use rows 19,001-20,000

I am using version 6.

 

[griffindm]

Sounds like a question of design more that function. Creating an 'IN' clause with over 1,000 items places a tremendous load on the database, as well as forcing the end user to screen through a lengthy picklist to determine what to include. Perhaps a better solution would be to use a stored procedure to populate a temp table with the data values to include (as rows) and then use an inner-join to that temp table to let the database run much more efficiently. (I'm assuming the picklist is for data to INCLUDE in the report, rather than exclude.)

Any commonality in the items selected in the picklist that may suggest another way to do the filter?

HTH,

Dave Griffin

 

[rxdatadude]

Users are not able to create tables on the Oracle server. Is this where the temp table would reside?

 

[griffindm]

Unfortunately for your case, yes. Another alternative might be to use cascading report picklists to filter down to a smaller expression for Oracle. In this method, you use a series of other reports to narrow a filter down. An example would be asking what department the report was for, and then using this as a filter for a report that then returns only the projects associated with that department. A final report would then only run for those projects. These can be several layers deep. I'm not sure if this would help you. Can you elaborate on the circumstances of your report filter?

Dave Griffin

 

[rxdatadude]

Someone sent me a file of ids from one catalog, then I have to hit those ids against another catalog looking for specific conditions. So, all ids will be selected from the picklist, but i'll only get results for those ids that meet the conditions in the second catalog.

 

[griffindm]

If you're always going to INCLUDE all id's from the file, consider using the MS Text ODBC driver to access the file as a datasource to create a hotfile. Then use the hotfile together with the other Oracle data to create the final report. Hotfiles can be used with any other catalog data and are useful for combining data from different databases. You can either join the data (i.e. table.id = hotfile.id) in the catalog or in the report itself.

Let me know if you think this would work and if you have further questions.

HTH

Dave Griffin