Why can't I use filter my WHERE clause on a CASE statement column in SQL Server?
case, sql, sql-server, where-clause
Solution
`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.
Problem
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?