Problem with CAST

• AIRWALKER-375999

  Hall of Fame

  Points: 3040

  More actions

  November 10, 2006 at 3:10 am

  #153286

  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

   

   

• Papillon

  Hall of Fame

  Points: 3303

  More actions

  November 10, 2006 at 3:14 am

  #671039

  hey

  you are using aggregate function for datatype of varchar....

  ---

  Regards,

  Papillon

• Rohit @ SQL

  SSC Eights!

  Points: 938

  More actions

  November 10, 2006 at 5:11 am

  #671042

  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

• AIRWALKER-375999

  Hall of Fame

  Points: 3040

  More actions

  November 10, 2006 at 9:02 am

  #671117

  Marvelous

  Thanks  guys for all your help.