The following code shows a straightforward example of a simple CASE expression.
SQL> SQL> drop table registrations; Table dropped.-- w w w.j av a 2 s .c o m SQL> create table registrations( 2 attendee NUMBER(4) not null, 3 course VARCHAR2(6) , 4 begindate DATE not null, 5 evaluation NUMBER(1) check (evaluation in (1,2,3,4,5))) ; SQL> SQL> insert into registrations values (7002,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7934,'SQL',date '1999-04-12',5 ); SQL> insert into registrations values (7006,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7011,'SQL',date '1999-04-12',2 ); SQL> insert into registrations values (7008,'SQL',date '1999-10-04',NULL); SQL> insert into registrations values (7009,'SQL',date '1999-10-04',3 ); SQL> insert into registrations values (7902,'SQL',date '1999-10-04',4 ); SQL> insert into registrations values (7902,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7006,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7003,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7012,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7902,'JSON',date '1999-08-10',5 ); SQL> insert into registrations values (7010,'JSON',date '2000-09-27',5 ); SQL> insert into registrations values (7002,'JAVA',date '1999-12-13',2 ); SQL> insert into registrations values (7007,'JAVA',date '1999-12-13',NULL ); SQL> insert into registrations values (7011,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7008,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7009,'JAVA',date '1999-12-13',4 ); SQL> insert into registrations values (7004,'JAVA',date '2000-02-01',3 ); SQL> insert into registrations values (7008,'JAVA',date '2000-02-01',4 ); SQL> insert into registrations values (7006,'JAVA',date '2000-02-01',5 ); SQL> insert into registrations values (7012,'XML',date '2000-02-03',4 ); SQL> insert into registrations values (7002,'XML',date '2000-02-03',5 ); SQL> insert into registrations values (7004,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7002,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7011,'PLS',date '2000-09-11',NULL); SQL> SQL> SQL> select attendee, begindate 2 , case evaluation 3 when 1 then 'bad' 4 when 2 then 'mediocre' 5 when 3 then 'ok' 6 when 4 then 'good' 7 when 5 then 'excellent' 8 else 'not filled in' 9 end 10 from registrations; ATTENDEE | BEGINDATE | CASEEVALUATIO --------- | --------- | ------------- 07002.00 | 12-APR-99 | good 07934.00 | 12-APR-99 | excellent 07006.00 | 12-APR-99 | good 07011.00 | 12-APR-99 | mediocre 07008.00 | 04-OCT-99 | not filled in 07009.00 | 04-OCT-99 | ok 07902.00 | 04-OCT-99 | good 07902.00 | 13-DEC-99 | not filled in 07006.00 | 13-DEC-99 | not filled in 07003.00 | 10-AUG-99 | good 07012.00 | 10-AUG-99 | good ATTENDEE | BEGINDATE | CASEEVALUATIO --------- | --------- | ------------- 07902.00 | 10-AUG-99 | excellent 07010.00 | 27-SEP-00 | excellent 07002.00 | 13-DEC-99 | mediocre 07007.00 | 13-DEC-99 | not filled in 07011.00 | 13-DEC-99 | excellent 07008.00 | 13-DEC-99 | excellent 07009.00 | 13-DEC-99 | good 07004.00 | 01-FEB-00 | ok 07008.00 | 01-FEB-00 | good 07006.00 | 01-FEB-00 | excellent 07012.00 | 03-FEB-00 | good ATTENDEE | BEGINDATE | CASEEVALUATIO --------- | --------- | ------------- 07002.00 | 03-FEB-00 | excellent 07004.00 | 11-SEP-00 | not filled in 07002.00 | 11-SEP-00 | not filled in 07011.00 | 11-SEP-00 | not filled in 26 rows selected. SQL>