The CASE conditional operator can implement the IF…ELSE…THEN logic without calling a subprogram. CASE conditional clauses include simple CASE clauses and CASE clauses with search conditions.
This topic describes how to use CASE clauses in queries.
Example 1: Use a simple CASE clause in a query and convert country codes into complete country names
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> SELECT id, abbr,
CASE abbr
WHEN 'US' THEN 'America'
WHEN 'UK' THEN 'English'
WHEN 'CN' THEN 'China'
ELSE 'UNKNOW'
END full_name
FROM t_case ;
+----+------+-----------+
| id | abbr | full_name |
+----+------+-----------+
| 1 | US | America |
| 2 | UK | English |
| 3 | CN | China |
| 4 | JP | UNKNOW |
+----+------+-----------+
4 rows in set
Example 2: Use a CASE clause with search conditions in a query
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
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
