select attendee, begindate
, case evaluation
when 1 then 'bad'
when 2 then 'mediocre'
when 3 then 'ok'
when 4 then 'good'
when 5 then 'excellent'
else 'not filled in'
end
from registrations
where course = 'S02';
ATTENDEE BEGINDATE CASEEVALUATIO
-------- --------- -------------
7499 12-APR-99 good
7698 12-APR-99 good
7698 13-DEC-99 not filled in
7788 04-OCT-99 not filled in
7839 04-OCT-99 ok
7876 12-APR-99 mediocre
7902 04-OCT-99 good
7902 13-DEC-99 not filled in
7934 12-APR-99 excellent
eg2.
-------- -------- -----
select ename, job
, case when job = 'TRAINER' then ' 10%'
when job = 'MANAGER' then ' 20%'
when ename = 'SMITH' then ' 30%'
else ' 0%'
end as raise
from employees
order by raise desc, ename;
ENAME JOB RAISE
-------- -------- -----
BLAKE MANAGER 20%
CLARK MANAGER 20%
JONES MANAGER 20%
ADAMS TRAINER 10%
FORD TRAINER 10%
SCOTT TRAINER 10%
SMITH TRAINER 10%
ALLEN SALESREP 0%
JONES ADMIN 0%
KING DIRECTOR 0%
note that SMITH gets only a 10% raise, despite the fourth line of the query. This is because he is a trainer, which causes the second line to result in a match; therefore, the remaining WHEN expressions are not considered.
