May 9, 2012 at 9:53 am
Hello,
I need to show products whose Status is NA and OnHand is greater than 0. But when I use below statement it is giving me error. So can somebody help me?
CREATE TABLE #Sample (Product int,Status varchar(2),OnHand int);
INSERT INTO #Sample Values (1230,'DI',20)
INSERT INTO #Sample Values (7689,'TO',20)
INSERT INTO #Sample Values (2341,'NA',0)
INSERT INTO #Sample Values (6758,'NA',10)
SELECT * FROM #Sample
SELECT * FROM #Sample
WHERE CASE
WHEN Status = 'NA' THEN OnHand > 0
ELSE OnHand
DROP TABLE #Sample
May 9, 2012 at 10:01 am
...
I need to show products whose Status is NA and OnHand is greater than 0. ...
You don't need to use CASE WHEN for what you asked. Just:
SELECT * FROM #Sample
WHERE Status = 'NA' AND OnHand > 0
The above will return all records from #Sample which have Status 'NA' and OnHand greater than 0
May 9, 2012 at 10:13 am
I'm sorry I didn't explain it correctly. I need all products and products with status NA must have onhand greater than zero. Below is what i want to display:
ProductStatusOnHand
1230DI20
7689TO20
6758NA10
May 9, 2012 at 10:18 am
Shree-903371 (5/9/2012)
I'm sorry I didn't explain it correctly. I need all products and products with status NA must have onhand greater than zero. Below is what i want to display:ProductStatusOnHand
1230DI20
7689TO20
6758NA10
SELECT * FROM #Sample
WHERE (Status = 'NA' AND OnHand > 0) OR (Status != 'NA')
OR, with CASE WHEN
SELECT * FROM #Sample
WHERE CASE WHEN Status = 'NA' AND OnHand <= 0 THEN 0 ELSE 1 END = 1
BTW. I would use the one without CASE WHEN!
May 9, 2012 at 10:46 am
Thank you it solved my issue.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply