Use CASE functions in queries

CASE expressions let you use the IF...THEN...ELSE logic in SQL statements without having to invoke subprograms. You can use simple CASE expressions or searched CASE expressions as needed.

Examples

Example 1

Use a simple CASE expression in a query to convert country codes into full country names.

1. Create a table named t_case.

   obclient> CREATE TABLE t_case(id number NOT NULL PRIMARY KEY, abbr varchar(5));
   Query OK, 0 rows affected
   

2. Insert data into the t_case table.

   obclient> INSERT INTO t_case VALUES (1,'US'),(2,'UK'),(3,'CN'),(4,'JP');
   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

3. Use a CASE expression to query full country names corresponding to country codes.

   obclient> SELECT id, abbr,
             CASE abbr
             WHEN 'US' THEN 'America'
             WHEN 'UK' THEN 'English'
             WHEN 'CN' THEN 'China'
             ELSE 'UNKOWN'
             END full_name
             FROM t_case ;
   +----+------+-----------+
   | ID | ABBR | FULL_NAME |
   +----+------+-----------+
   |  1 | US   | America   |
   |  2 | UK   | English   |
   |  3 | CN   | China     |
   |  4 | JP   | UNKOWN    |
   +----+------+-----------+
   4 rows in set
   

Example 2

Use a searched CASE expression in a query.

1. Create a table named t_case2.

   obclient> CREATE TABLE t_case2(id number NOT NULL PRIMARY KEY, c_date date );
   Query OK, 0 rows affected
   

2. Insert data into the t_case2 table.

   obclient> INSERT INTO t_case2(id,c_date)
             VALUES (1,to_date('2019-03-01','yyyy-mm-dd'))
             ,(2,to_date('2019-05-08','yyyy-mm-dd'))
             ,(3,to_date('2019-07-07','yyyy-mm-dd'))
             ,(4,to_date('2019-10-11','yyyy-mm-dd'))
             ,(5,to_date('2019-12-12','yyyy-mm-dd'))
             ,(6,to_date('2020-01-05','yyyy-mm-dd'));
   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

3. Use a searched CASE expression to query data in the Duration column.

   obclient> SELECT id, c_date,
                  CASE
                 WHEN months_between(sysdate, c_date) > 12 THEN 'More than one year ago'
                 WHEN months_between(sysdate, c_date) > 9 THEN 'More than three quarters ago'
                 WHEN months_between(sysdate, c_date) > 6 THEN 'More than half a year ago'
                 WHEN months_between(sysdate, c_date) > 3 THEN 'More than a quarter ago'
                 WHEN months_between(sysdate, c_date) >= 0 THEN 'Within a quarter'
                 ELSE 'Illegal'
                 END "Duration"
                 FROM t_case2;
   +----+-----------+------------------------+
   | ID | C_DATE    | Duration               |
   +----+-----------+------------------------+
   |  1 | 01-MAR-19 | More than one year ago |
   |  2 | 08-MAY-19 | More than one year ago |
   |  3 | 07-JUL-19 | More than one year ago |
   |  4 | 11-OCT-19 | More than one year ago |
   |  5 | 12-DEC-19 | More than one year ago |
   |  6 | 05-JAN-20 | More than one year ago |
   +----+-----------+------------------------+
   6 rows in set