November 10, 2006 at 3:10 am
Hello again.
Im using these cast statements of part of my query:
SUM(CASE CAST(IA.IAT_DATA AS VARCHAR(25)) WHEN 'Financial Reporting' THEN 'a' AS ELSE 0 END) AS [Financial Reporting],
SUM(CASE CAST(IA.IAT_DATA AS VARCHAR(25)) WHEN 'Performance Management' THEN 'b' ELSE 0 END) AS [Performance Management],
SUM(CASE CAST(IA.IAT_DATA AS VARCHAR(25)) WHEN 'Maths' THEN 'c' ELSE 0 END) AS [Maths]
problem when I execute the query I get the following error message:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'a' to a column of data type int.
I then changed the 3 cast statements to:
SUM(CASE CAST(IA.IAT_DATA AS int) WHEN 'Financial Reporting' THEN 'a' ELSE 0 END) AS [Financial Reporting],
when I do that I get the following error msg:
Server: Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type ntext to int is not allowed.
Can anybody see where I going wrong with the syntax? Thanks in advance
November 10, 2006 at 3:14 am
hey
you are using aggregate function for datatype of varchar....
Regards,
Papillon
November 10, 2006 at 5:11 am
Try this one
SUM(CASE CAST(IA.IAT_DATA AS VARCHAR(25)) WHEN 'Financial Reporting' THEN 1 AS ELSE 0 END) AS [Financial Reporting],
SUM(CASE CAST(IA.IAT_DATA AS VARCHAR(25)) WHEN 'Performance Management' THEN 1 ELSE 0 END) AS [Performance Management],
SUM(CASE CAST(IA.IAT_DATA AS VARCHAR(25)) WHEN 'Maths' THEN 1 ELSE 0 END) AS [Maths]
From IA
Rohit
November 10, 2006 at 9:02 am
Marvelous
Thanks guys for all your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply