0

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
saikumar
  • 21
  • 2

3 Answers3

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thank you , I got it that here after End it is an column alias. so it should not contain table alias ... thank you – saikumar Jul 31 '20 at 08:12
  • @saikumar - I've added a note about the missing grade for exactly 65 marks that Gordon referred to in a comment. – Alex Poole Jul 31 '20 at 08:15
0

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)

KOBER
  • 59
  • 1
  • 6
0

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;
Jim Macaulay
  • 4,709
  • 4
  • 28
  • 53