# Conditional where clause

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;
}
``````
###### Who is Participating?

Commented:
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

Commented:
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 Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.