Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
Switch statement construct. Evaluates the specified expression/condition and returns the result from the matching expression.
(CASE
WHEN condition THEN val1
WHEN condition THEN val2
...
ELSE defaultval END)
Switching off a single expression:
(CASE expression
WHEN case1 THEN val1
WHEN case2 THEN val2
...
ELSE defaultval END)
The return type depends on the type of arguments val1
, val2
, etc. For example, if val1
or val2
is a string, the return type will be a string, depending on which condition is met. If they are both integers, the return type will be an integer, and so on.
CASE
statements are evaluated sequentially, and the evaluation stops with the first matching condition. If there are multiple matching conditions, the value of the first matching WHEN
clause is returned.CASE
statements have two formats:
WHEN
conditions, and it returns the result from the first WHEN
condition that returns TRUE
.WHEN
clause which satisfies the condition.WHEN
clauses evaluate to the CASE
expression or return TRUE
, the CASE
statement returns the expression specified in the ELSE
clause.The following example shows how to use a CASE
statement using only one condition.
SELECT * FROM allviews;
****
+-------+---------+-------+
| State | Product | Views |
+-------+---------+-------+
| CA | E | 50 |
| NY | C | 15 |
| NY | F | 40 |
| CA | A | 60 |
| CA | D | 10 |
| NY | B | 20 |
| NULL | G | 40 |
+-------+---------+-------+
SELECT Views,
(CASE State
WHEN 'CA' THEN 'California'
WHEN 'NY' THEN 'New York'
ELSE 'N/A'
END) As 'State'
FROM allviews;
****
+-------+------------+
| Views | State |
+-------+------------+
| 50 | California |
| 15 | New York |
| 40 | New York |
| 60 | California |
| 10 | California |
| 20 | New York |
| 40 | N/A |
+-------+------------+
The following example shows how to use CASE
statements with independent WHEN
conditions.
SELECT
( CASE
WHEN 1=0 THEN 3
WHEN 0=1 THEN 5
ELSE 8
END) AS 'Choices';
****
+---------+
| Choices |
+---------+
| 8 |
+---------+
The following example shows that the value of only the first matching WHEN
clause is returned.
SELECT
( CASE 'ohai'
WHEN 'yo' THEN 'sup'
WHEN 'hello' THEN 'hi'
WHEN 'ohai' THEN 'bai'
WHEN 'ohai' THEN 'ok'
ELSE 'huh?'
END) As 'Greeting';
****
+----------+
| Greeting |
+----------+
| bai |
+----------+