Case statement 1

[axism]

I understand that you can't use IIF within select statement. I am trying to use Case Statement. Here's scenarior, if test > 50 pass, else fail. Studends can have scores from 1 to 100. I tried the following, which is not working. I am hoping to see if anyone can help me fix it.

select

case grade
when test > 50 then
pass
else
fail
end

from tblexams

 

[gmmastros]

You're close. You need to put apostrophes around pass and fail so that sql server knows that it is data and not another field.

select

case
when test > 50 then
'pass'
else
'fail'
end As PassFail

from tblexams

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[axism]

George,
thanks for replying. I tried that it seems like the problem relays on test > 50, says Incorrect syntax near '>'.

 

[SQLDenis]

The code that George posted is correct

create table tblexams (test int)
insert tblexams values(1)
insert tblexams values(11)
insert tblexams values(51)
insert tblexams values(55)
insert tblexams values(66)

select case
when test > 50 then
'pass'
else
'fail'
end As PassFail

from tblexams

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Notice that I removed grade immediately following the case.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

The field that you are testing can go between the Case and the When OR after the when. Here is an example showing it both ways.

Declare @tblExams Table(test integer)

Insert Into @tblExams Values(100)
Insert Into @tblExams Values(90)
Insert Into @tblExams Values(80)
Insert Into @tblExams Values(70)
Insert Into @tblExams Values(60)
Insert Into @tblExams Values(50)
Insert Into @tblExams Values(40)
Insert Into @tblExams Values(30)

Select test,
       Case When [!]test[/!] > 50
            Then 'pass'
            Else 'fail'
            End As Grade
From   @tblExams

Select Case [!]test[/!] 
            When 100 Then 'A'
            When 90 Then 'A'
            When 80 Then 'B'
            When 70 Then 'C'
            When 60 Then 'D'
            Else 'fail'
            End As Grade
From   @tblExams

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[axism]

I am sorry, that i over loooked the grade after case. It works out great. Thank you George and Denis.

 

[gmmastros]

You're welcome. When posting, I like to highlight things in [!]red[/!] to draw your attention to it. Since I removed something, I couldn't highlight it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom