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

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?

Answer 1

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.