CODEWITHSUNDEEP
sql
shishir_bindu
shishir_bindu

Reputation: 53

inserting values when null

I have two tables like

Student:

roll    Name
1       A
2       B
3       C

Mark

roll    Mark
1       85
3       95

I want a output like

roll    Name    Grade
1       A       A
2       B       F
3       C       A+

I have tried this query 

select Stdent.Roll,Stdent.Name,Grade= case 
when (Mark between 0 and 79) then ('F')
when (Mark between 80 and 89) then ('A')
when (Mark between 90 and 100) then ('A+') end  
from Mark 
right join Stdent 
on Mark.roll=Stdent.Roll 
order by Stdent.Roll

Output shows null in 2nd row of grade column. But I want it to be F.

Upvotes: 0

Views: 93

Answers (4)

Viru
Viru

Reputation: 533

select Stdent.Roll,Stdent.Name,Grade= case 
 when (NVL(Mark,0) between 0 and 79) then ('F')
 when (Mark between 80 and 89) then ('A')
 when (Mark between 90 and 100) then ('A+') end  
from Mark 
 right join Stdent 
 on Mark.roll=Stdent.Roll 
order by Stdent.Roll

Use NVL function to handle null values

Upvotes: 0

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

Try this:

select Stdent.Roll,Stdent.Name,Grade= case 
when (IsNull(Mark, 0) between 0 and 79) then ('F')
when (Mark between 80 and 89) then ('A')
when (Mark between 90 and 100) then ('A+') end  
from Mark 
right join Stdent 
on Mark.roll=Stdent.Roll 
order by Stdent.Roll

Upvotes: 0

GarethD
GarethD

Reputation: 69819

You need to handle Mark being NULL in your case statement:

SELECT  Student.Roll,
        Student.Name,
        Grade = CASE 
                    WHEN Mark.Mark BETWEEN 0 AND 79 OR Mark.Mark IS NULL THEN 'F'
                    WHEN Mark.Mark BETWEEN 80 AND 89 THEN 'A'
                    WHEN Mark.Mark BETWEEN 90 AND 100 THEN 'A+'
                END  
FROM    Mark 
        RIGHT JOIN Student 
            ON Mark.roll=Student.Roll 
ORDER BY Student.Roll

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

select Stdent.Roll,Stdent.Name,Grade= case 
when (func(Mark,0) between 0 and 79) then ('F')
when (Mark between 80 and 89) then ('A')
when (Mark between 90 and 100) then ('A+') end  
from Mark 
right join Stdent 
on Mark.roll=Stdent.Roll 
order by Stdent.Roll

You should use ISNULL instead of func if you are using sql server, if oracle nvl. Check this page for others.

Upvotes: 1

Related Questions