0

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

New contributor
Mark_72 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
11

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.

1
  • Unfortunately, we don't have the actual schema definition for the table. I decided to simply assume that OP's logic was correct, and therefore the invoice total is something like DECIMAL (10,2) NOT NULL. Made my answer cleaner. 😁
    – AMtwo
    Jan 14 at 13:01
3

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

2
  • +1 for "WHERE before SELECT" - it's a fundamental "about face" in thinking about how things are done that most people new to relational databases miss.
    – Phill W.
    Jan 14 at 12:18
  • Indeed, I normally start writing my queries SELECT 1 just to keep the stupid Intellisense happy, then add the columns after. BETWEEN is another common mistake Jan 14 at 12:19
1

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
New contributor
vampir is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • 2
    Hi, please add some text to your answer, to explain what you did & why. Answers that are just an alternative query, without any explanation or context are much less helpful to the future folks who might find this and are experiencing the same problem
    – AMtwo
    Jan 14 at 13:52
  • ok, thanks for tip
    – vampir
    2 days ago

Your Answer

Mark_72 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.