Why can't I use filter my WHERE clause on a CASE statement column in SQL Server?

Question

In my select statement there is a CASE WHEN THEN ELSE END AS statement that I am not able to filter on in my WHERE clause. I do not see why this would be an issue, could someone shed some light?

SELECT        
CASE 
    WHEN m.Country IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO') THEN 'NA' 
WHEN m.Country IN ('BRAZIL') THEN 'JD2' 
    WHEN m.Country IN ('NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA') THEN 'ANZ' 
ELSE 'Unknown' 
    END AS DerivedRegion,
    m.ID,
    m.[Account Name], 
m.[Display Name], 
m.[Last Name], 
m.[First Name]
FROM dbo.Users AS m
WHERE DerivedRegion = 'Unknown'

There WHERE clause gives me the error: Invalid column name 'DerivedRegion', why?

Unrelated, but if you only want the unknown regions, then what is the point of the case statement in the select? All of your results should say 'unknown' in the derived region column. — Jenn, Jun 05 '14 at 16:05
There was more script than just what I showed, it was unnecessary for this question. — LaRae White, Jun 05 '14 at 17:53

score 25 · Accepted Answer · answered Jun 05 '14 at 14:45

WHERE is processed before SELECT. It doesn't know what DerviedRegion is at that point. I'd recommend using a NOT IN in this case to exclude the list of countries. However, if you really want to use your CASE you could do something like this

SELECT *
FROM
(
    SELECT        
        CASE 
            WHEN m.Country IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO') THEN 'NA' 
            WHEN m.Country IN ('BRAZIL') THEN 'JD2' 
            WHEN m.Country IN ('NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA') THEN 'ANZ' 
            ELSE 'Unknown' 
        END AS DerivedRegion,
        m.ID,
        m.[Account Name], 
        m.[Display Name], 
        m.[Last Name], 
        m.[First Name]
    FROM dbo.Users AS m
) AS x
WHERE x.DerivedRegion = 'Unknown'

Check out MSDN and scroll down to Logical Processing Order of the SELECT statement to see the order in which a query is processed.

score 0 · Answer 2 · answered Jun 05 '14 at 14:48

You should repeat all in´s in the where clause:

SELECT        
    CASE 
        WHEN m.Country IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO') THEN 'NA' 
        WHEN m.Country IN ('BRAZIL') THEN 'JD2' 
        WHEN m.Country IN ('NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA') THEN 'ANZ' 
        ELSE 'Unknown' 
    END AS DerivedRegion,
    m.ID,
    m.[Account Name], 
    m.[Display Name], 
    m.[Last Name], 
    m.[First Name]
FROM 
    dbo.Users AS m
WHERE 
    m.Country NOT IN ('CANADA', 'UNITED STATES', 'USA', 'MEXICO', 'BRAZIL', 'NZ', 'NEW ZEALAND', 'AUSTRALIA', 'AUSTRALASIA')

Other solution could be: save the query as view without the where-clause (eg "myview") and then select from the view:

SELECT * FROM myview WHERE DerivedRegion = 'Unknown'

In this case, SQL server can better plan the execution.

Why can't I use filter my WHERE clause on a CASE statement column in SQL Server?

2 Answers2