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

• Compares when_base against each value in values_to_match using equality
• Returns the corresponding then value for the first match
• values_to_match and then arrays must have the same length
• All then values and else value must be of compatible types
• More concise than CASE_WHEN for simple equality comparisons

Examples

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