RE: Error in CASE statement – SQLServerCentral

SSC Guru

Points: 186127

November 26, 2014 at 1:38 am

Eirikur Eiriksson (11/26/2014)
Quick suggestion, combine the conditionals, consider this example which does the same as your code
😎
SELECT
CASE
WHEN Division = 'AWT' AND Product_Line = 'N' AND Product_Type = 'AG' AND AWT_Tank_Size IN (120,250,499,500,1000) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'N' AND Product_Type = 'UG' AND AWT_Tank_Size IN (120,250,500,1000,1990) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'N' AND Product_Type = 'AG/UG' AND AWT_Tank_Size IN (120,250) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'N' AND Product_Type = 'UG/SD' AND AWT_Tank_Size IN (250,500,1000) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'N' AND Product_Type = 'V' AND AWT_Tank_Size IN (60,120) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'PO' AND Product_Type = 'AG' AND AWT_Tank_Size IN (250,325,500) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'RF' AND Product_Type = 'AG' AND AWT_Tank_Size IN (250,325,500) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line = 'RF' AND Product_Type = 'UG' AND AWT_Tank_Size IN (250,325,499,500,1000) THEN AWT_Tank_Size
WHEN Division = 'AWT' AND Product_Line IN ('RF','PO','N') THEN 9999
ELSE 0
END

With this number of alternatives, I'd code it exactly like this too. It's far more readable and the performance is likely to be indistinguishable from the original. With more alternatives I'd code it as a #temp table and left-join to it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden