Darren came to me today requesting some help with an SQL problem had run into; he was trying to return some conditional data using a ‘SELECT CASE’ in his query, but he kept being served NULL values for all the rows that did not match his ‘WHEN’ statements. He tried to check if the returned value from the CASE was a NULL in a WHERE clause:
select
case
when shipcountry = 'norway' and @year1 = datepart(yy, orderdate) then employeeid
when shipcountry = 'brazil' and @year2 = datepart(yy, shippeddate) then employeeid
end
from orders
where 1 is not null
That didn’t work because what he was checking was whether the column value was NULL or not and not the value of the actual value returned from the CASE. That’s when I suggested the following example to Darren:
select employeeid
from orders
where orderid in (
select
case
when shipcountry = 'norway' and @year1 = datepart(yy, orderdate) then orderid
when shipcountry = 'brazil' and @year2 = datepart(yy, shippeddate) then orderid
end
)
This works perfectly and does exactly what he wanted — you have got to love the power of nested queries ;)
NOTE: the actual sample queries have been modified for the Northwind database for your testing pleasure.