problem of champ type

[foryou]

Hi I need to put 'abs' for students absent in a review for exemple of the material which the code is 121 . I have the following query:

SELECT id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE [121] END AS m1,
[122] AS '2', [123] AS '3'
FROM (SELECT id_field, id_student, NOTE, CODE FROM TEST)
p PIVOT (sum(NOTE) FOR id_field IN ([121], [122], [123])) AS pvt
ORDER BY id_student

I have the error here CASE WHEN (CODE) = 1 THEN 'Abs' because [121]is the type float and 'abs' is a text how to solve this? Thanks!!

[Ashfield]

Change your select to make 121 a char

SELECT id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE '121' END AS m1

or

SELECT id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE convert(varchar,121) END AS m1

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[foryou]

Thank you very much Ashfield. but the problem is in 'abs' text in float champ :(.

[Ashfield]

I know, you want the literal value 'abs' or 121, so if you force your value 121 to become a char type, either by convert (or cast) or just by enclosing it in quotes then sql server will treat BOTH values as char types and will be quite happy (as far as I know). Try it and see, if it still doesn't work post your new code and the error message again.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[foryou]

Thanks Ashfield!merci beaucoup i have anouther problem with this code ,I posted a new message. Thanks :)