Post reply

Incorrect syntax near the keyword 'ELSE'

  • November 10, 2014 at 9:49 pm

    #291804

    Hi Everyone,

    I am receiving the error message 'Incorrect syntax near the keyword 'ELSE'.' from the query section below -

    CASE WHEN T3.LineTotal IS NULL THEN ((T0.Rate * SUM(T0.LineTotal)) - ((T0.DiscPrcnt / 100)) ELSE (((T0.Rate * SUM(T0.LineTotal)) - (T0.DiscPrcnt / 100)) + T3.LineTotal) END AS 'Total NATIVE'

    If anybody can suggest how to fix this error it will be greatly appreciated.

    Kind Regards,

    David

  • November 10, 2014 at 10:40 pm

    #1757603

    Try this:

    CASE WHEN T3.LineTotal IS NULL THEN T0.Rate * SUM(T0.LineTotal) - (T0.DiscPrcnt / 100) ELSE ((T0.Rate * SUM(T0.LineTotal)) - (T0.DiscPrcnt / 100)) + T3.LineTotal) END AS 'Total NATIVE'

    It looks like parens where mismatched. Of course I could be way off base here. Without seeing the whole query it is a guess at best.

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • November 10, 2014 at 10:51 pm

    #1757607

    Quick note, as LinksUp noted, the parentheses do not match, there is an extra opening one before the first instance of T0.DiscPrcnt. To prevent/detect this kind of errors, I find it easier to fold the CASE statement.

    😎

    CASE

    WHEN T3.LineTotal IS NULL THEN ((T0.Rate * SUM(T0.LineTotal)) - (T0.DiscPrcnt / 100))

    ELSE (((T0.Rate * SUM(T0.LineTotal)) - (T0.DiscPrcnt / 100)) + T3.LineTotal)

    END AS 'Total NATIVE'

  • November 11, 2014 at 1:46 am

    #1757617

    I find it easier to use ISNULL:

    SELECT (T0.Rate * SUM(T0.LineTotal) - (T0.DiscPrcnt / 100) + ISNULL(T3.LineTotal,0) AS 'Total NATIVE'

    😉

    “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

  • November 11, 2014 at 2:31 pm

    #1757801

    Thanks for everyone's generous responses - the extra parenthesis was definitely the issue!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply