Oracle SQL - Simple CASE Expression Example

Introduction

The following code shows a straightforward example of a simple CASE expression.

Demo

SQL>
SQL> drop table registrations;

Table dropped.-- from  ww w . j a va2  s .  c  om

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>