Nested CASE

1

I've been fiddling with a nested CASE statement and getting nowhere. I've tried several approaches. The below probably illustrates best what I need to accomplish. Can anyone give me nudge in the right direction?

SELECT CASE ItemStock.WSSite WHEN 'Yes'
CASE ItemStock.Inv WHEN '5' THEN 'Yes' WHEN '0' THEN 'No'
END AS InStock,
CASE ItemStock.WSSite WHEN 'No' THEN 'No'
END AS InStock,

FROM ItemStock

2 
SELECT CASE ItemStock.WSSite WHEN 'Yes' 
    THEN CASE ItemStock.Inv WHEN '5' THEN 'Yes' WHEN '0' THEN 'No' END ELSE 'No' END AS InStock,
CASE ItemStock.WSSite WHEN 'No' THEN 'No' END AS InStock
3

What do you want the result to be when ItemStock.Inv is not equal to 5 and it is not equal to 0?

You could do the following:

SELECT CASE WHEN ItemStock.WSSite = 'Yes' AND ItemStock.Inv = '5' THEN 'Yes' ELSE 'No' END AS InStock

But - it all depends on the actual conditions that identify the item as being InStock. Is the item only in stock when WSSite = 'Yes' AND Inv = '5'? Or - are there other conditions?

1 Like
4

Just a slight tweak and I'm in business! Thank you very much!

SELECT CASE ItemStock.WSSite WHEN 'Yes' THEN
CASE ItemStock.Inv WHEN '5' THEN 'Yes' WHEN '0' THEN 'No' END
ELSE 'No' END AS InStock
FROM ItemStock