SQL CASE when using "AS"

Question

I am aiming to create a report for our stock system, to show whether we are IN stock or OUT of stock of products A B & C. Our stock is held in two columns named physical_qty and allocated_qty

Using this query, I am able to subtract physical_qty from allocated_qty and show it as stock

SELECT     
   warehouse, product, description, physical_qty - allocated_qty AS stock
FROM         
   scheme.stockm
WHERE     
   (warehouse = '01') AND (product IN ('A', 'B', 'C'))

This gives me a numerical value of the amount of stock that we have available to sell.

How can I evaluate if stock is < 1 show OUT otherwise show IN?

Thanks

Answer 1

Try

CASE WHEN physical_qty - allocated_qt < 1 THEN 'OUT' ELSE 'IN' END

Answer 2

Like this:

SELECT     warehouse, product, description, 
physical_qty - allocated_qty AS stock,
CASE WHEN (physical_qty - allocated_qty) < 1 THEN 'OUT'
ELSE 'IN' END AS Stock_Status
FROM         scheme.stockm
WHERE     (warehouse = '01') AND (product IN ('A', 'B', 'C'))