Conditional Functions - Kinetica Docs

Conditional functions are subject to short-circuiting to aid in error-checking.

DECODE(expr, match_a, value_a, ..., match_N, value_N[, unmatched_value])

Evaluates expr: returns the first value whose corresponding match is equal to expr; returns the optional unmatched_value (or null), if no match is found

IF(expr, value_if_true, value_if_false)

Evaluates expr: if true, returns value_if_true; otherwise, if false or null, returns value_if_false

Parameter	Description
`expr`	any true/false condition When an integer column is used directly, this function will interpret non-zero values as true and zero values as false.
`value_if_true`	any type; must be the same type as `value_if_false`
`value_if_false`	any type; must be the same type as `value_if_true`

CASE

The case statement acts as a scalar function, but has two more complex forms. Note that for each of these CASE statements, the value expressions must all be of the same or convertible data type. In the first form, each WHEN is followed by a conditional expression whose corresponding THEN expression will have its value returned, if true. Control will continue through each WHEN until a match is found and the corresponding value returned; if no match is found, the value of the ELSE expression will be returned, or null, if no ELSE clause exists.

CASE without Expression Syntax

CASE
    WHEN <cond_expr_a> THEN <value_expr_a>
    ...
    WHEN <cond_expr_n> THEN <value_expr_n>
    ELSE <value_expr>
END

In the second form, the CASE expression is evaluated. A match of that result will be attempted against each WHEN expression until a match is found and the value of the corresponding THEN expression returned; if no match is found, the value of the ELSE expression will be returned, or null, if no ELSE clause exists.

CASE with Expression Syntax

CASE <expr>
    WHEN <match_expr_a> THEN <value_expr_a>
    ...
    WHEN <match_expr_n> THEN <value_expr_n>
    ELSE <value_expr>
END

This second version below has greater optimization than the first.

CASE without Expression Example

CASE
    WHEN color = 1 THEN 'Red'
    WHEN color >= 2 THEN 'Green'
    ELSE 'Blue'
END

CASE with Expression Example

CASE MOD(LENGTH(text), 2)
    WHEN 0 THEN 'Even'
    WHEN 1 THEN 'Odd'
    ELSE null
END

​CASE

CASE