Cross Tab query giving error - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Cross Tab query giving error

Author

Topic

vaidyanathanpc
Starting Member

24 Posts

Posted - 2002-05-08 : 02:28:16

Hi,
I'm having a problem while writing cross tab queries. The fact is I'm getting errors when I execute the query.
The query is as follows.

SELECT skill_Id,
count(CASE final_rating WHEN 5 THEN assoc_name ELSE 0 END) AS "5",
count(CASE final_rating WHEN 4 THEN assoc_name ELSE 0 END) AS "4",
count(CASE final_rating WHEN 3 THEN assoc_name ELSE 0 END) AS "3",
count(CASE final_rating WHEN 2 THEN assoc_name ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id

My purpose is to get the count of the assoc_name for each final_rating grouped by skill_id. When I execute the above query I'm getting the error

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Name1' to a column of data type int.

Name1 is an assoc_name in the view viewfinalrating. Why is SQL server trying to convert the assoc_name to int. Isn't the query supposed to get the count of assoc name based on the CASE statements?

What could be the problem?

Thanks in advance
Regards,
P.C. Vaidyanathan

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-05-08 : 04:11:08

The datatypes in the case statement should be the same

You could use ' ' instead of 0

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-05-08 : 05:03:29

Use NULL instead of 0

e.g.
count(CASE final_rating WHEN 5 THEN assoc_name ELSE NULL END) AS "5",

Ramesh

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-05-08 : 05:07:14

I'm not sure of what mean by

quote:

get the count of the assoc_name"

if you wish to get number of occurrences of each rating/skill_Id,

SELECT skill_Id,
sum(CASE final_rating WHEN 5 THEN 1 ELSE 0 END) AS "5",
sum(CASE final_rating WHEN 4 THEN 1 ELSE 0 END) AS "4",
sum(CASE final_rating WHEN 3 THEN 1 ELSE 0 END) AS "3",
sum(CASE final_rating WHEN 2 THEN 1 ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources