It is common knowledge that you can apply a Case statement in the SELECT or ORDER BY portion of a SQL statement. What isn’t well known is that you can use it effectively in a WHERE clause.
Take an example I just ran into:
I want to show all orders to every employee unless the order is assigned to employeeid 263, EXCEPT if the person trying to view all orders IS employeeid 263 or if it is that persons boss. In that case, show every order.
This appears really simple and could be handled in a where clause, but I couldn’t figure it out. And even if there is a better way, why not use a CASE statement just for fun anyway:
select * from Orders
where
case
when @loginid = 263 or @loginid = 181
then employeeid
when (@loginid <> 263 and @loginid <> 181) and employeeid = 263
then 0
else employeeid
end
= employeeidThe first when says “if you are #263 or #181, set employeeid = employeeid, which will always be true and will the order.
The second when says, “if you aren’t #263 or #181, and the current order is assigned to #263, then set t.employeeid = 0” which will always be false, and never show the order.
The tricky part of adapting the Case statement is realizing you are only setting one side of an equation, and the second part, the “= employeeid” follows after the “end”.
8 responses to “Using a Case Statement in a SQL Where Clause”
Thanks for the post. Been trying to do some things with a similar situation at work and found this helpful.
Coincidentally, you can put a case statement on both sides of the = sign like so:
where
case (boolean) then (expression) else (expression) end =
case (boolean) then (expression) else (expression) end
As long as both sides of the operator have valid syntax.
Hi, Thank for the code, i really need this type of functionaliy, thanks again…!!!
I’m trying to use Case in the where clause, but I’m new to SQL and am not sure if that will even work or how to make it work with what I desire.
What I desire is below…can anyone help?
Where
status = ‘pay’ and reason is null
and status = ‘approval’ reason is null
and status = ‘exception’ and reason is not null
and status = ‘exception’ and reason1 is not null
and status = ‘exception’ and reason2 is not null
that doesn’t look like you can use a case statement. just a huge if…ou just need a lot of parenthesis and all your “and””s should be “or”‘s since you cant have a record that has both status=pay and status=approval at the same time.
WHERE
((status = ‘pay’ and reason is null)
OR (status = ‘approval’ and reason is null))
AND
((status = ‘exception’ and reason is not null)
OR (status = ‘exception’ and reason1 is not null)
OR (status = ‘exception’ and reason2 is not null))
Thanks so much for this post! I read another one that said a case statment was not possible in a where clause, but I actually really had the need for it. So, now my query gets me exactly what I need.
Not sure its a Coincidence… :p
What if i want to change the condition
Why is some thing like below not possible?
where var1
case when comparer = ‘G’ then
<=
else
<=
end
'conditionvalue'
Your comment is awaiting moderation.
What if i want to change the condition
Why is some thing like below not possible?
where var1
case when comparer = ‘G’ then
= — change condition here
end
‘conditionvalue’