Incorrect syntax near the keyword 'ELSE'

Question

Incorrect syntax near the keyword 'ELSE'

david.dartnell

Ten Centuries

Points: 1276
More actions
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

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

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

LinksUp SSCertifiable Points: 6526 More actions · Answer 1

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/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

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'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

david.dartnell Ten Centuries Points: 1276 More actions · Answer 4

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