This topic provides specific examples on how to use CASE clauses in queries.
CASE conditional operator
The CASE conditional operator lets you use the IF...THEN...ELSE logic in SQL statements without having to invoke subprograms. You can use simple CASE clauses or searched CASE clauses as needed.
Examples
Create a table and insert proper data into the table.
obclient> CREATE TABLE t_case(id NUMBER NOT NULL PRIMARY KEY, abbr VARCHAR(5));
Query OK, 0 rows affected
obclient> INSERT INTO t_case(id, abbr) VALUES (1,'US'),(2,'UK'),(3,'CN'),(4,'JP');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> CREATE TABLE t_case2(id NUMBER NOT NULL PRIMARY KEY, c_date DATE );
Query OK, 0 rows affected
obclient> INSERT INTO t_case2(id,c_date) VALUES (1,'2021-03-01'),(2,'2021-05-08'),(3,'2021-07-07'),(4,'2021-10-11'),(5,'2021-12-12'),(6,'2022-01-05');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
Use a simple CASE clause in a query
Use a simple CASE clause in a query to convert country codes into full country names.
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
Use a searched CASE clause in a query
obclient> SELECT id, c_date,
CASE
WHEN DATEDIFF(NOW(), c_date) > 12*30 THEN 'More than one year ago'
WHEN DATEDIFF(NOW(), c_date) > 9*30 THEN 'More than three quarters ago'
WHEN DATEDIFF(NOW(), c_date) > 6*30 THEN 'More than half a year ago'
WHEN DATEDIFF(NOW(), c_date) > 3*30 THEN 'More than a quarter ago'
WHEN DATEDIFF(NOW(), c_date) >= 0 THEN 'Within a quarter'
ELSE 'Illegal'
END "Duration"
FROM t_case2;
+----+------------+------------------------------+
| id | c_date | Duration |
+----+------------+------------------------------+
| 1 | 2021-03-01 | More than one year ago |
| 2 | 2021-05-08 | More than three quarters ago |
| 3 | 2021-07-07 | More than half a year ago |
| 4 | 2021-10-11 | More than a quarter ago |
| 5 | 2021-12-12 | Within a quarter |
| 6 | 2022-01-05 | Within a quarter |
+----+------------+------------------------------+
6 rows in set