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

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

Examples

Numeric base

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

String base

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

Suggest changes to this page
Last update at: 2026/06/20 10:08:10