I have updated this one as:
--BEGIN TRAN ROLLBACK
SELECT
CASE WHEN Division = 'AWT' THEN
CASE WHEN Product_Line = 'N' THEN
CASE WHEN Product_Type = 'AG' THEN
CASE WHEN AWT_Tank_Size = 120 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 499 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 500 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 1000 THEN AWT_Tank_Size
ELSE 9999
END
ELSE
CASE WHEN Product_Type = 'UG' THEN
CASE WHEN AWT_Tank_Size = 120 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 500 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 1000 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 1990 THEN AWT_Tank_Size
ELSE 9999
END
ELSE
CASE WHEN Product_Type = 'AG/UG' THEN
CASE WHEN AWT_Tank_Size = 120 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
ELSE 9999
END
ELSE
CASE WHEN Product_Type = 'UG/SD' THEN
CASE WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 500 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 1000 THEN AWT_Tank_Size
ELSE 9999
END
ELSE
CASE WHEN Product_Type = 'V' THEN
CASE WHEN AWT_Tank_Size = 60 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 120 THEN AWT_Tank_Size
ELSE 9999
END
ELSE 0
END END END END END
ELSE ---getting error
CASE WHEN Product_Line = 'PO' THEN
CASE WHEN Product_Type = 'AG' THEN
CASE WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 325 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 500 THEN AWT_Tank_Size
ELSE 9999
END
ELSE 0
END
--ELSE 0 END
ELSE
CASE WHEN Product_Line = 'RF' THEN
CASE WHEN Product_Type = 'AG' THEN
CASE WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 325 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 500 THEN AWT_Tank_Size
ELSE 9999
END
ELSE
CASE WHEN Product_Type = 'UG' THEN
CASE WHEN AWT_Tank_Size = 250 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 325 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 499 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 500 THEN AWT_Tank_Size
WHEN AWT_Tank_Size = 1000 THEN AWT_Tank_Size
ELSE 9999
END
ELSE 0
END END
ELSE 0 --getting error
END END END
ELSE 0 --getting error
END
--FROM cnsmr
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/