Case Statement in a view? 1

[jeepxo]

I'm trying to create a view that takes a table with 4 columns and makes it appear as if it were 8 columns.
It's based on a marks spread for a large number of students.
The original table layout is:

course_code |School_Year | Student_ID | Mark

What I need to convert it to is
course_code | School_year | Student_ID | FailingGrade | Level1 | Level2 | Level3 | Level4

Where a mark below 50% is FailingGrade
50% to 59.9% is Level 1
and so on.

I'm using 1 to represent true and 0 to be false so that I can do a quick sum on the column.
So if a student had 55%, there would be a 0 in Failing Grade, a 1 in Level1, 0 in Level2 etc.

I thought that I could do

Select case  
when marks < 50 then FailingGrade = 1
when marks >= 50 and marks < 60 then Level1 = 1
when marks >=60 and marks <70 then Level2 = 2
end, course_code, School_year from myGradeTable

But I get

"ORA-00905: missing keyword" at
when marks < 50 then FailingGrade = 1
at the = sign

Any Suggestions on how I can do this?

 

[LKBrwnDBA]

You need a case for each column:

Select 
case when marks < 50 then 1 else 0 end as FailingGrade,
case when marks >= 50 and marks < 60 then 1 else 0 end as Level1,
...etc...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[cmmrfrds]

Do you want separate columns for each, it is not clear?

Select
case when marks < 50 then 1 else 0 end as FailingGrade,
case when marks >= 50 and marks < 60 then 1 else 0 end as Level1,
case when marks >=60 and marks <70 then 1 else 0 end as Level2,
etc ............

 

[jeepxo]

DOH!

Some times you can't see the trees because of the forest!

&quot;Every day is like a precious gift, you have to make it count&quot; James Birrell 1993-2001