Case in WHERE clause, Incorrect Syntax near >

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case in WHERE clause, Incorrect Syntax near >

Author

Topic

mmccardle
Starting Member

43 Posts

Posted - 2010-10-01 : 11:08:08

I am getting a syntax error with this code:


WHERE lst_trx_date BETWEEN @start AND @end AND
(ISNUMERIC(LEFT(LTRIM(job),1))=1 OR job LIKE 'S%' OR job LIKE 'A%') AND
CASE
	WHEN (ISNUMERIC(LEFT(LTRIM(job),1))=1 OR job LIKE 'A%') 
        THEN (SELECT sum(qty_moved) FROM jobroute WHERE job.job=jobroute.job AND job.suffix=jobroute.suffix GROUP BY jobroute.job) > 0
END

There error message is this: Msg 102, Level 15, State 1, Procedure GVC_JobStatusReport, Line 52
Incorrect syntax near '>'.

My goal is that I want to select jobs that either start with a number, an S or an A. If the job starts with a number or A, the qty_moved needs to be greater than 0. If the job starts with S, I want it to show up no matter what the qty_moved is. Each jobs has multiple "operations" stored on a different table, hence the sub-query in the case expression.

If I take out the CASE expression, it runs fine but it filters numeric, A jobs and S jobs on the criteria. I want the criteria to ignore S jobs.

jleitao
Posting Yak Master

100 Posts

Posted - 2010-10-01 : 11:32:03

hi mmccardle,

your code return error because you put a condition (> 0) in the "THEN part"

WHERE lst_trx_date BETWEEN @start AND @end AND
(
(job LIKE 'S%')
OR
((ISNUMERIC(LEFT(LTRIM(job),1))=1 OR job LIKE 'A%')
AND ((SELECT sum(qty_moved) FROM jobroute WHERE job.job=jobroute.job AND job.suffix=jobroute.suffix GROUP BY jobroute.job) > 0))
)

I dont test it but i think it works!

JLeitao

mmccardle
Starting Member

43 Posts

Posted - 2010-10-01 : 11:56:10

Hmmmmmmm you got some boolean skills! Thanks that worked!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 07:05:15

Note that isnumeric is not fully reliable
select isnumeric('10d2')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources