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 be NULL.
All WHEN ... expressions must have the same data type.
All THEN ... and the ELSE 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
----+------------
| Toddler
| Child
| Teenager
| Teenager
| Young adult
| Adult
| 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