SQL Server 2000 CASE Statement Error
-
Hello,
Basically what I am trying to do is per agency id, give me a grouping of techarea and then tell me how many entries are there. If the count comes up as 0 then count the number of times the ID# is there for the null techarea and give me that result. I am using the CASE statement which is equivalent to the Access Iif statement from what I have read.
I have the following written in SQL Query Analyzer:
SELECT App.agency_id,App.techarea AS TA,
CountOfTA = CASE App.techarea
WHEN (COUNT(App.techarea)<1) THEN COUNT(App.agency_id)
WHEN (COUNT(App.techarea)>1) THEN COUNT(App.techarea)
FROM dbo.App
WHERE App.agency_id = 999
GROUP BY App.agency_id
And I am getting the following error message:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '<'.
As far as I know < is an accepted operator. Any ideas why I am getting the error message that I am. I did find out it is a syntax error message but I can't find any error.
Thx for any help you can provide.
CLM
-
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 -
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
-
I think you are missing techarea from your GROUP BY as well...
Good Hunting!AJ Ahrens
webmaster@kritter.net -
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
-
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.
-
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
-
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
-
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply