SYNTAX ERROR - please assist...

Question

SYNTAX ERROR - please assist...

sgmunson

SSC Guru

Points: 110640
More actions
June 19, 2008 at 12:21 pm

#192946

Hi all,
I'm getting 3 syntax errors from something in the following construct:
SELECT 'EXPENSE',6300,'611-8110 -Transfer In from General Fund',
CASE WHEN ((SELECT SUM(AMOUNT) FROM GROUP_IT_REV WHERE LNUM BETWEEN 5970 AND 6210) -
(SELECT SUM(AMOUNT) FROM GROUP_IT_EXP WHERE LNUM BETWEEN 639 0AND 6600)) < 0.00
THEN ABS((SELECT SUM(AMOUNT) FROM GROUP_IT_REV WHERE LNUM BETWEEN 5970 AND 6210) -
(SELECT SUM(AMOUNT) FROM GROUP_IT_EXP WHERE LNUM BETWEEN 639 0AND 6600))
ELSE 0.00
END AS AMOUNT
The error messages are:
Msg 102, Level 15, State 1, Line 1822
Incorrect syntax near '0'.
Msg 102, Level 15, State 1, Line 1823
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1824
Incorrect syntax near '0'.
This part of my query is UNION ALL ed with the rest of a rather long query that I know runs fine without this additional mess. I'm not sure if this is a valid construction or not, and am open to alternate suggestions. I'm unable to use temp tables, so for any table alternatives, please use a table variable. The table structure for what this result set is intended to UNION ALL with follows:
(
MAIN_TAG varchar(10),
LNUM smallint,
LTAG varchar(65),
AMOUNT decimal(18,2)
)
Thanks in advance for helping me see what's wrong here...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

I don't see anything obviously wrong with the syntax, but could this be caused by one (or more) of the sub-queries is returning a NULL?

Try wrapping each sub-query with COALESCE and see if the problem goes away.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sgmunson SSC Guru Points: 110640 More actions · Answer 2

I get the syntax error just from checking the query without even attempting to run it. I tried using ISNULL for each query, but that didn't change anything, unfortunately. Any other ideas?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Like RoseAnn Rosanna-Danna says - NEVER MIND !!!!

I'm such a dope today. Take a good close look at the zeros just before the word AND. They were supposed to be next to the previous number you see, instead of with the AND. Dang what a way to waste some serious time...

( bops self in head and say's "I coulda had a V8 !!! )

Steve

(aka smunson)

:w00t::w00t::w00t:

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

When you double click on the error, it takes you to the line causing the problem... now, lemme ask... do you see anything at all wrong with the following?

BETWEEN 639 0AND 6600

"Must look eye..."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

Yep - that would do it. Dang, didn't even see it myself - probably should put my glasses on :hehe:

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Sorry Steve... you got there before I did... must've been just a couple of seconds because we both posted at the same time. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Yup, that's what I figured. I've done that myself any number of times, despite significant experience in a forum environment. You go to post and either someone beats you to it (and in so darn many cases, has an even better idea!!), or you don't realize there's a 2nd or greater page to the thread that you haven't even seen yet...:P:P;):w00t::hehe:

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)