Thomas Johansen's Blog

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.