Converting data types in SQL case statement

I need to have the following happen in a select query when s.grade is a data type smallint.

CASE
WHEN s.grade = 13 THEN 'Grad'
ELSE s.grade
END as Grade

I haven't found any examples quite like mine where a smallint can be converted to a specific word varchar in a case statement.

Thanks
smantzDirector of TechnologyAsked:
Who is Participating?
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
WHEN s.grade = 13 THEN 'Grad' 
ELSE convert(varchar, s.grade)
END as Grade

Open in new window

0
 
smantzDirector of TechnologyAuthor Commented:
Not sure exactly how that works but it seems to have done the trick.  I'll do more checking to verify

Thanks
0
 
smantzDirector of TechnologyAuthor Commented:
Hey Thanks again,

I had to make an adjustment as, at 2009 the database went through some major conversions so, even graduated students up till last year showed as grade 12.

Solution: I used their Status field A (Active),  I (Inactive), G (Graduated)

WHEN s.Status = 'G' THEN 'Grad'
ELSE convert(varchar, s.grade)
END as Grade

That seemed to do the trick
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
when using a case statement they have to return the same DataType.  

(EG you can't do a varchar and an int).

Using the convert makes the int a varchar and sql will treat the column as a varchar column rather than an int column.
0
 
Ryan ChongCommented:
WHEN s.Status = 'G' THEN 'Grad'
ELSE convert(varchar, s.grade)
END as Grade

just a comment, wondering if you can do it with a mapping table, so that you can get rid of potential case ... when statements there, and create a single editing point for what should be returned for the Status field.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.