Why is SQL Server not processing the codes correctly?

Question

Here is my code:

Select 
   cname, 
   case
       WHEN cid < 35 then 'Failed'
       WHEN cid > 35 AND < 50 then 'Below Average'
       WHEN cid > 50 AND < 60 then 'Average'
       WHEN cid > 60 AND < 70 then 'Good'
       WHEN cid > 70 AND < 85 then 'Distinction'
       WHEN cid > 85 then 'Outstanding' 
   end as Report 
from cus

I don't know what was the wrong with above code. It is not getting executed correctly.

It is showing the result likes this.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.

May I know what mistake I have made, and how to overcome it ?

score 2 · Accepted Answer · answered Mar 14 '14 at 05:58

Change your case to

Select cname, case
WHEN cid < 35 then 'Failed'
When cid >35 and cid<50 then 'Below Average'
WHEN cid >50 and cid<60 then 'Average'
WHEN cid >60 and cid<70 then 'Good'
WHEN cid >70 and cid<85 then 'Distinction'
WHEN cid >85 then 'Outstanding' end as Report from cus

You forgot to specify cid for the second condition.

As a side not, what happens if it is exaclt 50?

You might want to change it to inclusive/exclusive cluases. Something like

Select cname, case
WHEN cid <= 35 then 'Failed'
When cid >35 and cid<=50 then 'Below Average'
WHEN cid >50 and cid<=60 then 'Average'
WHEN cid >60 and cid<=70 then 'Good'
WHEN cid >70 and cid<=85 then 'Distinction'
WHEN cid >85 then 'Outstanding' end as Report from cus

score 0 · Answer 2 · answered Mar 14 '14 at 06:13

Try this

Select 
   cname, 
   case
       WHEN cid < 35 then 'Failed'
       WHEN cid > 35 AND cid  < 50 then 'Below Average'
       WHEN cid > 50 AND cid  < 60 then 'Average'
       WHEN cid > 60 AND cid  < 70 then 'Good'
       WHEN cid > 70 AND cid  < 85 then 'Distinction'
       WHEN cid > 85 then 'Outstanding' 
   end as Report 
from cus

Why is SQL Server not processing the codes correctly?

2 Answers2