User Defined Selection in Oracle 9i SQL

[Abslag]

I am working on a report that allows the user to define the information they receive. Normally that is not a problem when I use "like", "between","=","<",">" etc... but I am trying to use "in" so they can enter multiple items separated by a comma and also enter a nvl if the space if blank.

I would expect it to look something like this but it doesnt work.
FROM customer_order_line col
where {table Name} in
nvl(('&Order_Type','&Order_Type','&Order_Type'),'%')

That is supposed to allow them to enter the order_type field up to 3 values separated by a comma and if they leave it blank it will return all values for that parameter.

I know I could do several lines something like
{xyz table name} = nvl('&Order_type1','%') and
{xyz table name} = nvl('&Order_Type2','%') and
{xyz table name} = nvl('&Order_Type3','%') etc..

but in the selection they would get three separate fields to enter data. I would like it all combined in one so they can just enter the same info and separate it with a comma.

If you have any input I would love to hear from you. Hopefully the question makes enough sense.

 

[lewisp]

What you are asking for can only be acheived in PL/SQL using dynamic SQL, I believe. Your second solution is the only one that will work in raw SQL.

What tool are you using for your report engine? In Oracle reports you can use lexical parameters to acheive what you want.

 

[lewisp]

Actually, you second solution would need the = changed to LIKE to work as well.....

 

[Abslag]

Lewisp,
I will often use Crystal but in this case I chose not to because the report will be exported to Excel and my other option is just a little simpler for the user.

My other report engine is part of our ERP system (IFS) but there I have to use raw SQL.

You are right about needing LIKE also, I caught that after I had already posted the message.

Thanks anyway

 

[Abslag]

I decided to use my secondary option but realized there is a problem with it as well. I am using the same table/same field for all three of the Order_Type selections and because I have a nvl so that if the user decides to leave that field blank then it will return all rows. My problem is that if they decide to only enter one Order_Type and leave the other two blank, it will return the Order_Type they selected, AND everything else since the nvl,xx,'%' is telling it to include those as well. Any help would be totally appreciated.

 

[Abslag]

Does my question make sense above?