CASE statement issue

Can anyone help with this error?
SQL:
SELECT InvoiceDate, BillingAddress, BillingCity, Total,
CASE WHEN Total < 2.00 THEN 'Baseline Purchase' WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performer' END AS PurchaseType
FROM
invoices
WHERE
PurchaseType = 'Top Performer'
ORDER BY
BillingCity
Error: Msg 207, Level 16, State 1, Line 13 Invalid column name 'PurchaseType'.

This error is coming from your
WHERE
clause, not yourCASE
expression.You say
WHERE PurchaseType = 'Top Performer'
, however,PurchaseType
is not a column in your table, so the query optimizer doesn't know what to do with it in as a search predicate.There are a few ways to resolve this. One option is to copy/paste your
CASE
expression twice to appear in both theWHERE
andSELECT
clauses:SELECT InvoiceDate, BillingAddress, BillingCity, Total,
CASE WHEN Total < 2.00 THEN 'Baseline Purchase' WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performer' END AS PurchaseType
FROM
invoices
WHERE
CASE
WHEN Total < 2.00 THEN 'Baseline Purchase'
WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
ELSE 'Top Performer'
END = 'Top Performer'
ORDER BY
BillingCity
This is kind of "icky" because you're repeating yourself, and if you change that
CASE
expression, you'll need to change it in two places.You could simplify the logic in the
WHERE
clause to not even need to useCASE
, since you're filtering out anything but the "Top Performers" :SELECT InvoiceDate, BillingAddress, BillingCity, Total,
CASE WHEN Total < 2.00 THEN 'Baseline Purchase' WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performer' END AS PurchaseType
FROM
invoices
WHERE Total > 15.00
ORDER BY
BillingCity
Or you could pop your query into a CTE, and then reference the CTE to perform your filtering. This will allow the optimizer to figure out that you want to do the computation of the
CASE
expression, then filter on the output of that:WITH InvoiceData AS ( SELECT InvoiceDate, BillingAddress, BillingCity, Total,
CASE WHEN Total < 2.00 THEN 'Baseline Purchase' WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase' WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase' ELSE 'Top Performer' END AS PurchaseType FROM invoices
)
SELECT *
FROM InvoiceData
WHERE
PurchaseType = 'Top Performer'
ORDER BY
BillingCity
edit: Since we don't have the table schema, the above queries all assuming the
Total
column is defined as something likeDECIMAL(10,2) NOT NULL
. If the column allowsNULL
values, or values more precise than 2 digits, then theCASE
statement itself would need additional changes. Thanks to https://dba.stackexchange.com/users/993 for pointing out that I hadn't mentioned these additional potential pitfalls initially.