Solved

# Conditional where clause

Posted on 2009-05-05
2,318 Views
Hello,

I'm having a problem where I have a WHERE-clause which depends on input from the user.

My first thought was something similar to the switch-statement you find in some languages but I can't seem to find anything similar.

A CASE is not acceptable since that returns a value and I need to set conditions. And I cannot use stored procedures or similar either; it all has to be done in a SELECT.

Anyone got any ideas? See pseudo-code below.
``````SELECT
something
FROM
somewhere
WHERE
switch(input)  {
case 100:
field1 = input
break;
case 101:
field1 = input OR field1 = 200
AND
field2 IN (1,2,3)
break;
case 102:
field1 = input
AND
field2 IN (2,5,7)
break;
case 103:
field1 = input
break;
}
``````
0
Question by:Elisas
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 74

Accepted Solution

sdstuber earned 500 total points
ID: 24303577
yes, use case like this...

SELECT something
FROM somewhere
WHERE CASE
WHEN input = 100 AND field1 = input THEN 1
WHEN input = 101 AND (field1 = input OR field1 = 200 AND field2 IN (1, 2, 3)) THEN 1
WHEN input = 102 AND field1 = input AND field2 IN (2, 5, 7) THEN 1
WHEN input = 103 AND field1 = input THEN 1
ELSE 0
END = 1
0

LVL 15

Expert Comment

ID: 24308861
Hi,

Your switch statement is just a OR condition:

where  ( input = 100 AND field1 = input )
or         ( input = 101 AND (field1 = input OR field1 = 200 AND field2 IN (1, 2, 3)) )
or         ( input = 102 AND field1 = input AND field2 IN (2, 5, 7) )
or         ( input = 103 AND field1 = input )

Regards,
Franck.
0

Author Closing Comment

ID: 31577964
Excellent. I was stumbling a bit along those lines but I guess I didn't manage to wrap my mind properly around it then. Much obliged.
0

LVL 6

Expert Comment

ID: 24311864
One of possible solutions :

select * from some_table
where field1 = case when switch in (100, 103) then input end
or (switch = 101 and (field1 = input or field1 = 200) and (field2 in (1,2,3)))
or (switch = 102 and (field1 = input and field2 in (2,5,7)))

See attached sample.

Sample.txt
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
###### Suggested Courses
Course of the Month8 days, 2 hours left to enroll