oracle-01795: maximum number of expressions in the list is 1000

[ahlone]

Hi,

In ReportNet, I have a report, which has the "select & search prompt" prompt for material. And, it has 97,000 records for material downdown list, and, when the report is run with the option of "select all" for material start with 06%. Then, the number of rows for material is over 3000 records, &, the report will errored out with this error:
"DPR-ERR-2082 An error has occurred. Please contact your administrator. The complete error has been logged by CAF with SecureErrorID:2006-02-16-17:04:53.603-#59"

And, it the error log, the error will be as follow:
"oracle-01795: maximum number of expressions in the list is 1000"


Does anybody have experience this problem & how do you solve it? Or, for above example with material 06%, how do I limit the number of records to less than 1000, &, so, I can ran it 3 or 4 different times or so.....

Thanks....

 

[KingCrab]

Umm.. first question, why in the world would you place 97000 elements into a drop down? That is bad design.

However, you can solve this by adding a filter:

Material=?MaterialPrompt? or ?MaterialPrompt?='ALL'

Add a static value to your list report name "All Materials" with a value of 'ALL'..
Pick that item to get a all list.

Still, 97000 records in a drop down in just not right. I would pick some other type of prompt or a higher level, something,.

 

[ahlone]

Thanks for the quick response, KingCrab. Actually, we just implemented Cognos ReportNet a couple of month ago. &, consultants are gone, and, I am pretty new at this, but, I have some experience with the oracle report. And, I am not sure why so many records are in the drop-down list. Currently, the material is being filtered with "select & search prompt" prompt. It looks like this:

Material in ?MaterialPrompt?

?? So, are you saying that I should change the operator to = instead of in?
?? I am not too clear on the part of adding static value. For example, I want to run the report for all the materials, which start with 06% (3678 records), how could I get the static records to be 999 records or, less than 1000 records. With, that static records, I have to run the reports 4 times for all materials starting with 06%.

I really appreciated your help.......

 

[KingCrab]

You can use IN
Material IN ?MaterialPrompt? or ?MaterialPrompt?='ALL'

 

[JGirl]

The error message that you are getting is an Oracle error rather than a ReportNet error. The oracle database is complaining that your SQL query's IN(...) clause has more than 1000 items. I dont know if this is something you can change in oracle.

Are you selecting distinct material values in your prompt query?

My suggestion would be to see if there is a higher level grouping that you could filter by first, and then cascade the prompts to reduce the number of selections.

Alternatively, if you need a report that filters materials by the first few numbers/letters/whatever maybe you could create a substring column that only contains the first few characters of the material and use that in the filter?

 

[ahlone]

Thanks JGirl,
I checked with Oracle database side, &, they said that is the limit.

material select statement has dictinct selection as below:

*************************************************************
select distinct
Material.MATERIAL_ID as Material_Identifier,
Material.MATERIAL_DSC as Material_Description,
Material.c5 as c3
from
(select
Material.MATERIAL_ID as MATERIAL_ID,
Material.MATERIAL_DSC as MATERIAL_DSC,
case when ((Material.MATERIAL_ID = '-') and (Material.MATERIAL_DSC = '«Unknown»')) then '«Unknown»' else ((Material.MATERIAL_ID || '-') || Material.MATERIAL_DSC) end as c5
from
MartPrd...MATERIAL Material
) Material
order by
Material_Description asc

*************************************************************

For highler level grouping, the report has material type & material group VALUE PROMPT. OK, I will trying cascading the prompt, but, the user is doing select all on all the value prompt, so, let me see how it's going to work. Thanks........

 

[shwetabh]

Check to see if any limitation has been set on the query for the number of rows to be retrived in the following places.

--Click on the query and in the properties box, check for the "Maximum rows retreived".

If you do not found anything there, check he governor settingsfor the model.

 

[ahlone]

shwetabh, How do I check the governor setting for the model?

 

[devcog]

Hi,
Since, Oracle has a limitation on the number of values it can have in the IN clause, you'll have to make use of the LIKE operator to achieve the results.

So, replace Select & Search prompt with a TextBox prompt. And train the user to type in the first few characters of the filter value.
Then modify the filter criteria in Report Studio as follows:
Field starts with ?Prompt Value?

Hope this helps.

 

[griffindm]

Make the prompt optional. That way if they do not select anything in it, the query will not use it at all, thus bypassing the enormous IN clause that using 'Select All' would generate.

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
Magic with Data
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[ALSAP]

If you store a value of '_ALL' in the reference table used by the prompt, you can do something like we use here.

(1=if('_ALL' in (?PROMPT?))then(1) else if (COL in(?PROMPT?))then(1) else (0))

Using a space before ALL in the table will force it to appear first in the prompt list.

We've found that this method works well in getting around the ORA-01795: maximum number of expressions in a list is 1000 error!

 

[ProbablyDown]

I worked on Cognos ages ago... I remember a similar issue and the fix for us was something in the impromptu.ini file. I can't remember exactly what it was but perhaps it may point you in the right direaction.

 

[Draoued]

@ProbablyDown : this picklist limit doesn't show an Oracle error. So maybe this is not the case here.

anyway what you are referring to is in Impromptu.ini the Picklist limit.
default value is 100 (hundred).

[Startup Options]
picklist limit=1000