Why can't I use filter my WHERE clause on a CASE statement column in SQL Server?
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.
LaRae White
Software Engineer, Database Engineer, Graphics Programmer, Newbie C#/.NET, T-SQL, C++, JavaScript, VB, XML,JSON, HTML5, CSS3, SpecFlow/Gherkin/Cucumber I love lamp
Updated on June 06, 2020Comments
-
LaRae White almost 4 years
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?