CASE_WHEN_VALUE()

All functions > CONDITIONAL > CASE_WHEN_VALUE()

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

Signatures

Returns: the value corresponding to the first match, or else value if none match

CASE_WHEN_VALUE(when_base: T, values_to_match: ARRAY<T>, then: ARRAY<U>, else: U) → U

Notes

• The same logic is usually written as CASE <expr> WHEN … THEN … [ELSE …] END (see the CASE WHEN VALUE operator page). This function is the registry encoding.
• Compares when_base against each value in values_to_match using equality
• values_to_match and then arrays must have the same length
• All then values and else value must be of compatible types

Examples

SELECT
    -- Value 2 matches position 2, returns 'b'
    f1 := CASE_WHEN_VALUE(2, ARRAY(1, 2, 3), ARRAY('a', 'b', 'c'), 'z'),
    -- Value 5 not in array, returns else value
    f2 := CASE_WHEN_VALUE(5, ARRAY(1, 2, 3), ARRAY('a', 'b', 'c'), 'z'),
    -- String matching
    f3 := CASE_WHEN_VALUE(
        'cat',
        ARRAY('dog', 'cat', 'bird'),
        ARRAY(1, 2, 3),
        999
    )
;