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

asked 2022-01-13 by Mark_72

Answer

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.

answered 2022-01-13 by Andy Mallon