Using value prompt with Native SQL +1000 Records

[afzaki7]

Hi all

i am using a value prompt with a Native SQL in my query like this

Code Select Expand


......
select * from Results
where DERIVED_RESULT_PROPERTY_ID in    (#promptmany('ResultPropertyIDParam')#)


everything works fine but there is a problem when the IDs become more that 1000
the oracle gives me this error
ORA-01795: maximum number of expressions in a list is 1000

anyone know how to work around this issue using my prompt

Thanks in advance

[BigChris]

Looks like an Oracle limitation:

http://goo.gl/CT8s4b
http://goo.gl/n5OZuh

[afzaki7]

I failed to handle it

can you give me example of how to apply your solutions on my query ??

[BigChris]

Sorry, no idea, I'm on SQL server...just found some references on Google that I thought might be useful.

From what I could see it sounded like the limitation was at the Oracle end - I don't know if there's anything that can be done there to increase the limit. If there isn't, then I'd suggest you'd need to break your filter into x number of prompts, and put something on the prompt page to tell users that they can only put at most 1000 records into each prompt. You're filter would then need to be something along the lines of:

[DataItem] in ?Prompt_1st_1000? or [DataItem] in ?Prompt_2nd_1000 or [DataItem] in ?Prompt_3rd_1000 etc.

[bdbits]

Or look at a better way to do data selection than pasting in 1000+ IDs.

[Cinu8]

Guys there is a way to run the report with out error message.

Do not select the value's in the prompt jest click Ok