CASE statement issue

Question

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'.

Answer 1

This error is coming from your WHERE clause, not your CASE 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 the WHERE and SELECT 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 use CASE, 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 like DECIMAL(10,2) NOT NULL. If the column allows NULL values, or values more precise than 2 digits, then the CASE statement itself would need additional changes. Thanks to ypercubeᵀᴹ for pointing out that I hadn't mentioned these additional potential pitfalls initially.

Answer 2

Because of SQL's logical order of operations (WHERE is evaluated before SELECT), you cannot refer to a calculated value in the SELECT from the WHERE clause.

@AMtwo has provided some options. A cleaner solution in my opinion, is to place it into a CROSS APPLY (VALUES. Once you do that, you can refer to it in any later clauses.

SELECT 
    i.InvoiceDate, 
    i.BillingAddress, 
    i.BillingCity, 
    i.Total,
    v.PurchaseType
FROM 
    invoices i
CROSS APPLY (VALUES (
    CASE
      WHEN i.Total < 2.00 THEN 'Baseline Purchase'
      WHEN i.Total >= 2.00 AND i.Total < 7.00 THEN 'Low Purchase'
      WHEN i.Total >= 7.00 AND i.Total < 15.00 THEN 'Target Purchase'
      ELSE 'Top Performer'
    END
) ) v(PurchaseType)
WHERE
  v.PurchaseType = 'Top Performer'
ORDER BY 
  BillingCity;

Obviously you can simplify this particular query, by removing the cases which do not match the correct Total anyway, but this demonstrates the principle behind this technique.

Note also that BETWEEN can cause certain values to fall through the cracks. It is normally better to use a half-open interval >= AND <.

Answer 3

wrap your query and access from "outside" or in other words, query your query so you can use your composed fields as condition and grouping

This is alternative to CROSS APPLY (VALUES mentioned by @Charlieface that I use and I thinks it is clearest

SELECT * FROM (
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


) AS sq
WHERE
  PurchaseType = 'Top Performer'
ORDER BY 
  BillingCity