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 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 https://dba.stackexchange.com/users/993 for pointing out that I hadn't mentioned these additional potential pitfalls initially.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2