CASE Statement

The CASE statement is used in compound statements in stored programs.

Description

The CASE statement is used in compound statements in stored programs.

Note: There is also a CASE operator, which differs from the CASE statement described here. See Flow Control Functions. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

If no when_value matches and the CASE statement contains no ELSE clause, the CASE statement executes no branch and completes normally.

Each statement_list consists of one or more SQL statements; an empty statement_list is not permitted.

Syntax

Syntax 1

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

For this syntax, case_value is an expression. This value is compared to the when_value expression in each WHEN clause from left to right until one of them is equal. When an equal when_value is found, the corresponding THEN clause statement_list executes. If no when_value is equal, the ELSE clause statement_list executes, if there is one. The CASE statement itself does not return a value.

This syntax cannot be used to test for equality with NULL because the result of NULL = NULL is NULL, not true.

MatrixOne v4.2.8 supports the stored-program CASE syntax with case_value shown above; it does not support the searched CASE syntax without case_value.

Examples

CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER);
INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1);

mysql> SELECT CASE AVG (c0) WHEN any_value(c1) * any_value(c2) THEN 1 END FROM t1;
+------------------------------------------------------------+
| case avg(c0) when any_value(c1) * any_value(c2) then 1 end |
+------------------------------------------------------------+
|                                                          1 |
+------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CASE any_value(c1) * any_value(c2) WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1;
+--------------------------------------------------------------------------------+
| case any_value(c1) * any_value(c2) when sum(c0) then 1 when avg(c0) then 2 end |
+--------------------------------------------------------------------------------+
|                                                                              2 |
+--------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CASE any_value(c1) WHEN any_value(c1) + 1 THEN 1 END, ABS(AVG(c0)) FROM t1;
+------------------------------------------------------+--------------+
| case any_value(c1) when any_value(c1) + 1 then 1 end | abs(avg(c0)) |
+------------------------------------------------------+--------------+
|                                                 NULL |            1 |
+------------------------------------------------------+--------------+
1 row in set (0.00 sec)