CASE¶
A case expression can test a conditional expression, and depending on the result, evaluate additional expressions.
CASE
works like nested IF ... THEN ... ELSE ...
. When one of the conditions evaluates to TRUE
, the evaluation stops and the expression or operand assosicated with the result (THEN ...
) is evaulated. Otherwise, the evaluation continues to the next condition. If no conditional matches, the ELSE
result is returned.
Syntax¶
case_expression ::=
searched_case | simple_case
searched_case ::=
CASE
WHEN conditional_value_expr THEN result_value_expr
[WHEN ...]
[ELSE result_value_expr]
END
simple_case ::=
CASE value_expr
WHEN value_expr THEN result_value_expr
[WHEN ...]
[ELSE result_value_expr]
END
Arguments¶
Parameter | Description |
---|---|
conditional_value_expr |
A condition that evaluates to a boolean (TRUE , FALSE , or NULL ) |
value_expr |
A general value expression or a literal. |
result_value_expr |
A general value expression or a literal. All data types of result_value_expr must match within a CASE . |
Returns¶
Returns an expression consistent with the type of result_value_expr
.
Notes¶
- SQream DB does not support the
IF .. THEN .. ELSE
syntax. A simple case expression can replace it. - If no
ELSE
is specified, the default result will beNULL
. - All
WHEN ...
expressions must have the same data type. - All
THEN ...
and theELSE
results must have the same data type.
Simple case expression¶
simple_case ::=
CASE value_expr
WHEN value_expr THEN result_value_expr
[WHEN ...]
[ELSE result_value_expr]
END
A simple case expression evaluates the condition (conditional_value_expr
), and then evaluates and returns the result from the corresponding THEN
.
If no matches are found, NULL
is returned.
Searched case expression¶
searched_case ::=
CASE
WHEN conditional_value_expr THEN result_value_expr
[WHEN ...]
[ELSE result_value_expr]
END
A searched case expression evaluates every conditional_value_expr
listed. At the first conditional_value_expr
that evaluates to TRUE
, the result of the THEN
will be returned.
If no matches are found, NULL
is returned.
Examples¶
Simple case¶
master=> SELECT name, CASE num_eyes
. WHEN 1 THEN 'Cyclops'
. WHEN 2 THEN 'Binocular'
. WHEN 5 THEN 'Pentocular'
. WHEN 8 then 'Octocular'
. ELSE 'Other'
. END
. FROM (VALUES ('Copepod',1), ('Spider',8), ('Starfish', 5), ('Praying mantis', 5), ('Human (average)', 2), ('Eagle', 2), ('Horseshoe crab', 10))
. AS cool_animals(name, num_eyes);
name | ?column?
----------------+-----------
Copepod | Cyclops
Spider | Octocular
Starfish | Pentocular
Praying mantis | Pentocular
Human (average) | Binocular
Eagle | Binocular
Horseshoe crab | Other
Searched case¶
SELECT age, CASE
WHEN age < 3 THEN 'Toddler'
WHEN age < 12 THEN 'Child'
WHEN age < 20 THEN 'Teenager'
WHEN age < 34 THEN 'Young adult'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END AS "Age Group"
FROM (VALUES (2), (5), (15), (19), (32), (44), (87)) AS t(age);
age | Age group
----+------------
2 | Toddler
5 | Child
15 | Teenager
19 | Teenager
32 | Young adult
44 | Adult
87 | Senior
Replacing IF
with CASE
¶
As SQream DB does not support the IF
function found on some other DBMSs, use CASE
instead.
-- MySQL syntax:
IF (age > 65, "Senior", "Other")
is functionally identical to:
CASE
WHEN age > 65 THEN 'Senior'
ELSE 'Other'
END