help with nesting CASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with nesting CASE

Author

Topic

Dmh188
Starting Member

37 Posts

Posted - 2012-03-01 : 12:45:32

hey guys i think i need to use the case statement for what i am trying to do. Basically i am trying to get an Item associated with its Cost. But our DB is half a**ed and the value could be in a couple of different spots.

The value could be in either ir.lastcost, ir.std_cost, ir.cost, and i.cost. But some items have a value in multiple columns so i want to put in a specific order.

this is what i have now
' SELECT ir.item_code,
CASE ir.last_cost
WHEN 0 THEN ir.std_cost
WHEN 0 THEN ir.cost
WHEN 0 THEN i.cost
ELSE ir.last_cost END
FROM item_replenish_vendor ir
inner join items i ON i.itemno=ir.item_code
WHERE i.active = 't'
ORDER BY ir.item_Code '

basically i need it to look at IR.Last_Cost and if its 0 then look at the IR.STD_COST, and if that is 0 then at IR.Cost instead, and if that is 0 Then look at I.Cost.

I have tried a number of combination and cant seem to get it worked. This is the stripped down,easiest to read version. I know i need to do some nesting of CASE but not to sure.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-01 : 13:20:55

You might try a combination of NULLIF and COALESCE.. For example:

COALESCE(NULLIF(ir.last_cost,0), NULLIF(ir.std_cost, 0), NULLIF(ir.cost, 0), NULLIF(i.cost, 0))

Dmh188
Starting Member

37 Posts

Posted - 2012-03-01 : 13:23:42

that worked beautifully! thank you Lamprey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources