Error in CASE statement

  • 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/

  • 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.

    😎

  • 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/

  • 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.

  • 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/

  • 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/

  • 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

  • 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

  • Thanks all for your suggestion

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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