Case Statement in SQL

Question

Hi Everyone,

i have wrote below case statement in

SELECT OD.OrderID, OD.Quantity,
CASE
WHEN OD.Quantity > 30 THEN 1
WHEN OD.Quantity = 30 THEN 0
ELSE 0
END AS QuantityText,

CASE
WHEN OD.QuantityText = 1, THEN 'A'
ELSE 'B' as AB
FROM OrderDetails OD

When i use OD.QuantityText in second case statement i am receving error

Answer 1

There are 2 syntax error in your SQL, a comma after =1 and a missing END.

And you can not use a calculated column in an other calculation in the same SELECT statement, you could e.g. use a CTE like

;with cte as
    (SELECT OD.OrderID, OD.Quantity,
     CASE
     WHEN OD.Quantity > 30 THEN 1
     WHEN OD.Quantity = 30 THEN 0
     ELSE 0
     END AS QuantityText
     FROM OrderDetails OD)
select *,
CASE
WHEN cte.QuantityText = 1 THEN 'A'
ELSE 'B' END as AB
from cte

Answer 2

One way is to use derived table instead of CTE

 select *,
 CASE
 WHEN q.QuantityText = 1 THEN 'A'
 ELSE 'B' END as AB
 from (SELECT OD.OrderID, OD.Quantity,
      CASE
      WHEN OD.Quantity > 30 THEN 1
      WHEN OD.Quantity = 30 THEN 0
      ELSE 0
      END AS QuantityText
      FROM OrderDetails OD) q;

Tom