Use case when clause to decode value : CASE « Query Select « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Query Select
  3. CASE
SQL> create table registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)
  6  , constraint  R_PK        primary key (attendee,course,begindate)
  7  ) ;

Table created.

SQL>
SQL> insert into registrations values (2,'SQL',date '2009-04-12',4   );

1 row created.

SQL> insert into registrations values (14,'SQL',date '2009-04-12',5   );

1 row created.

SQL> insert into registrations values (6,'SQL',date '2009-04-12',4   );

1 row created.

SQL> insert into registrations values (11,'SQL',date '2009-04-12',2   );

1 row created.

SQL> insert into registrations values (8,'SQL',date '2009-10-04',NULL);

1 row created.

SQL> insert into registrations values (9,'SQL',date '2009-10-04',3   );

1 row created.

SQL> insert into registrations values (13,'SQL',date '2009-10-04',4   );

1 row created.

SQL> insert into registrations values (13,'SQL',date '2009-12-13',NULL);

1 row created.

SQL> insert into registrations values (6,'SQL',date '2009-12-13',NULL);

1 row created.

SQL> insert into registrations values (3,'OAU',date '2009-08-10',4   );

1 row created.

SQL> insert into registrations values (12,'OAU',date '2009-08-10',4   );

1 row created.

SQL> insert into registrations values (13,'OAU',date '2009-08-10',5   );

1 row created.

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
 11  where  course = 'SQL';

  ATTENDEE BEGINDATE  CASEEVALUATIO
---------- ---------- -------------
         2 12-04-2009 good
        14 12-04-2009 excellent
         6 12-04-2009 good
        11 12-04-2009 mediocre
         8 04-10-2009 not filled in
         9 04-10-2009 ok
        13 04-10-2009 good
        13 13-12-2009 not filled in
         6 13-12-2009 not filled in

9 rows selected.

SQL>
SQL>
SQL> drop table registrations;

Table dropped.








2.15.CASE
2.15.1.Using the CASE Expression
2.15.2.The following example illustrates the use of a simple CASE expression:
2.15.3.Using Searched CASE Expressions
2.15.4.The following example illustrates the use of a searched CASE expression:
2.15.5.Use logical operators in a searched CASE expression
2.15.6.Use CASE statement to deal with NULL
2.15.7.Use case when and grouping function together
2.15.8.Use case when clause to decode value
2.15.9.Use case when statement with between ... and
2.15.10.Use case when statement with exists and subquery
2.15.11.Use case when statement with in()
2.15.12.Use case when statement with to_char() like
2.15.13.Use case when with comparasion operator
2.15.14.Use Case to output null value
2.15.15.Wrap case when into sum() function