CASE WHEN VALUE

All functions > CONDITIONAL > CASE WHEN VALUE

Returns the result corresponding to the first matching value in a simple case expression

Syntax

CASE when_base WHEN value_to_match THEN result { WHEN value_to_match THEN result ... } [ ELSE default ] END

CASE when_base WHEN value_to_match THEN result { WHEN value_to_match THEN result ... } [ ELSE default ] END

Notes

• Write this as CASE <expr> WHEN <value> THEN <result> … [ELSE …] END: the base expression is compared for equality to each WHEN value in order; the first match picks its THEN result.
• Shorter than a searched CASE WHEN when every branch is an equality test on the same left-hand side.
• All THEN results and ELSE must be type-compatible. Use ELSE when you need a concrete result for non-matching values (the grammar for this form expects an ELSE in typical usage).

Related Functions

• CASE_WHEN_VALUE()

Examples

Numeric base

SELECT
    f1 := CASE 2 WHEN 1 THEN 'a' WHEN 2 THEN 'b' WHEN 3 THEN 'c' ELSE 'z' END, -- WHEN value equals base
    f2 := CASE 5 WHEN 1 THEN 'a' WHEN 2 THEN 'b' WHEN 3 THEN 'c' ELSE 'z' END, -- No WHEN match, ELSE
    f3 := CASE 10 WHEN 10 THEN 'ten' ELSE 'other' END -- Literal match on numeric base
;

String base

SELECT
    f1 := CASE 'cat' WHEN 'dog' THEN 1 WHEN 'cat' THEN 2 WHEN 'bird' THEN 3 ELSE 999 END, -- First matching string WHEN
    f2 := CASE 'x' WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 99 END -- No string match, ELSE
;