Error in using Case Expression in select statement while we used join in statements

Question

This code doesn't work and showing error "From keyword not found where expected"

select m.marks,
CASE 
WHEN m.marks<65 then 'F' 
WHEN m.marks>65 then 'P' 
END m.grade 
from student s INNER JOIN marks m ON s.id=m.id;

But this works

select marks, 
CASE 
WHEN marks<65 then 'F' 
WHEN marks>65 then 'P' 
END grade 
from marks

Answer 1

The m is an alias for the marks table, and you use that when referencing columns in that table. You are applying it to a column alias:

END m.grade 

where it does not belong, so - in that place only - remove the m.:

select m.marks,
CASE 
WHEN m.marks<65 then 'F' 
WHEN m.marks>65 then 'P' 
END grade 
from student s INNER JOIN marks m ON s.id=m.id;

As @Gordon pointed out in a comment, you are checking >65 and <65, so a student with exactly 65 will not get either F or P - the grade for them will be null. You probably want:

WHEN m.marks < 65 then 'F' 
WHEN m.marks >= 65 then 'P' 

or

WHEN m.marks >= 65 then 'P' 
ELSE 'F' 

... though if marks is null the second version will treat that as F too, while the first will still return null.

Answer 2

You cant use a column value as column Alias

Select 1 AS Alias_1,
CASE 
WHEN 1=1 THEN 1
WHEN 2=2 THEN 2
END AS Alias_2_AnyName
from dual d;

If you use

Select 1 AS Alias_1,
CASE 
WHEN 1=1 THEN 1
WHEN 2=2 THEN 2
END AS d.value
from dual d;

Oracle is expecting an Alias Name after your case block end, but instead you are adding another column value(As key could be omitted)

Answer 3

m.grade is the issue, it should be just grade as it is an alias

select m.marks,
CASE 
WHEN m.marks<65 then 'F' 
WHEN m.marks>65 then 'P' 
END grade 
from student s INNER JOIN marks m ON s.id=m.id;