November 25, 2014 at 12:49 pm
Hi all,
I have write a code using CASE statement but it gives me error.
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
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
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
ELSE 0 --getting error
END
ELSE 0 --getting error
END
FROM edb_Main
Error message: Incorrect syntax near the keyword 'ELSE'.
I have put comment where error is coming... please help
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 25, 2014 at 1:02 pm
Fundamental error here, ELSE is the last / unmatched clause, not a part of the higher case flow hierarchy, suggest you remove all but the last else statements within each case clause.
😎
November 25, 2014 at 1:12 pm
Eirikur Eiriksson (11/25/2014)
Fundamental error here, ELSE is the last / unmatched clause, not a part of the higher case flow hierarchy, suggest you remove all but the last else statements within each case clause.😎
Can you please explain, I am not getting what you said
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 25, 2014 at 1:19 pm
It looks like you have mismatched else statements, try rebuilding it slowly one case statement at a time. Also a lot of those case statements look redundant which will make trying to trouble shoot that thing as a whole that much more complicated.
November 25, 2014 at 2:07 pm
Yeah that thing needs a complete rewrite. Once you have nested case expressions it is good chance you can simplify. When they are nested this deep it is not longer a chance, it is way overly complicated and needs to be simplified. You can't maintain code like this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2014 at 12:19 am
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/
November 26, 2014 at 1:01 am
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
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😎
SELECTCASE
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.
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
November 26, 2014 at 2:03 am
Thanks all for your suggestion
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 27, 2014 at 5:34 am
Better still, Chuck the conditions into and @Table and join it in the query; it makes future maintenance MUCH easier.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply