SQL Server 2000 CASE Statement Error

Question

Post reply

SQL Server 2000 CASE Statement Error

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Try

CASE COUNT(App.techarea) WHEN < 1 THEN COUNT(App.agency_id) WHEN > 1 THEN COUNT(App.techarea) END AS CountOfTA

OR

you could just add the END reserved word after your 2nd THEN.....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

C.L. M. SSC Veteran Points: 256 More actions · Answer 2

I tried what you suggested but for some reason I am still getting an error message of :

Server: Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near '<'.

Any idea what might be causing this?

Thx,

CLM

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

I think you are missing techarea from your GROUP BY as well...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

C.L. M. SSC Veteran Points: 256 More actions · Answer 4

Thx AJ,

You are correct I was missing the 2nd group by field... however I still get the same error message.

Question... I have SQL Server Developer loaded onto my laptop, is there anything that could be wrong with the software load that might be causing this? I know with Access if you don't have the correct references selected it can cause erroneous error messages.

Let me know your thoughts.

CLM

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

You may be getting a null result. It would help to if you could provide at least a partial layout of the table App and some data to look at.

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Problem is in the CASE statement. You can't use the column name both in definition of the CASE (CASE App.techarea) and in the WHEN conditions (WHEN COUNT(App.techarea) > 1). This should work:

SELECT App.agency_id,App.techarea AS TA,

CountOfTA = CASE

WHEN (COUNT(App.techarea)<1) THEN COUNT(App.agency_id)

WHEN (COUNT(App.techarea)>1) THEN COUNT(App.techarea)

END

FROM dbo.App

WHERE App.agency_id = 999

GROUP BY App.agency_id, techarea

BTW, are you sure you got the conditions right? What if Count(App.techarea) = 1? This will result in NULL value in CountOfTA, which IMHO isn't what you need.

HTH, Vladan

C.L. M. SSC Veteran Points: 256 More actions · Answer 7

Vladan,

You are AWESOME!! It worked.

I thought VBA could be syntax sensitive, now I SQL Server wins that one.

Thanks for the assist.

CLM

Kevin Jones SSC Enthusiast Points: 114 More actions · Answer 8

In looking up CASE functions in SQL Server BOL, it appears that there are two different types of CASE functions: simple and searched.

Simple is to be used when you're comparing an exression to some other expressions:

CASE COUNT(App.techarea)

WHEN 5 THEN COUNT(App.agency_id)

WHEN 10 THEN COUNT(App.techarea)

END

Searched is to be used when evaluating Boolean expressions, which is what your CASE function is (the expression COUNT(App.techarea)<1 is Boolean, either true or false).

CASE COUNT(App.techarea)

WHEN (COUNT(App.techarea)<1) THEN COUNT(App.agency_id)

WHEN (COUNT(App.techarea)>1) THEN COUNT(App.techarea)

END

I think that's the correct interpretation of the content of the BOL for the CASE function. If I got it wrong or described it poorly, please correct me anyone.

Kevin