Conditionally set values passed to IN operator

[thermidor]

Hi All:

I want to write a query to conditionally set the values passed to an IN operator. Using the code below, I want to pass ('A', 'B') when someVariable = 'someVal'. Else I pass ('C', 'D', 'E'). Whatever I pass should filter the cur_data table on the key_letter column.

Any thoughts how to do this?

TIA,
Sven

SELECT colA FROM cur_data
WHERE key_letter IN
(CASE WHEN someVariable = 'someVal'
THEN ('A', 'B')
ELSE ('C', 'D', 'E')
END)

 

[Dagon]

The best you can do without using dynamic SQL is a solution based on using INSTR. See this idea from SantaMufasa.

http://www.tek-tips.com/viewthread.cfm?qid=1589238

For Oracle-related work, contact me through Linked-In.

 

[thermidor]

Thanks for the pointer Dagon. SantaMufasa has helped me many times!

 

[Beilstwh]

You could also do the following


SELECT colA FROM cur_data
WHERE (someVariable = 'someVal' and key_letter IN ('A','B'))
or (someVariable <> 'someVal' and key_letter IN ('C', 'D', 'E'));

Bill
Lead Application Developer
New York State, USA