CASE | Snowflake Documentation (original) (raw)

Categories:

Works like a cascading “if-then-else” statement. In the more general form, a series of conditions are evaluated in sequence. When a condition evaluates to TRUE, the evaluation stops and the associated result (after THEN) is returned. If none of the conditions evaluate to TRUE, then the result after the optional ELSE is returned, if present; otherwise NULL is returned.

In the second, “shorthand” form, the expression after CASE is compared to each of the WHEN expressions in sequence, until one matches; then the associated result (after THEN) is returned. If none of the expressions match, the result after the optional ELSE is returned, if present; otherwise NULL is returned.

Note that in the second form, a NULL CASE expression matches none of the WHEN expressions, even if one of the WHEN expressions is also NULL.

Syntax¶

CASE WHEN THEN [ WHEN THEN ] [ ... ] [ ELSE ] END

Arguments¶

_condition#_

In the first form of CASE, each condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL).

_expr_

A general expression.

_value_

In the second form of CASE, each _value_ is a potential match for _expr_. The _value_ can be a literal or an expression. The _value_ must be the same data type as the _expr_, or must be a data type that can be cast to the data type of the _expr_.

_result#_

In the first form of the CASE clause, if _condition#_ is true, then the function returns the corresponding _result#_. If more than one condition is true, then the result associated with the first true condition is returned.

In the second form of the CASE statement, if _value#_ matches the_expr_, then the corresponding _result_ is returned. If more than one _value_ matches the _expr_, then the first matching value’s _result_ is returned.

The result should be an expression that evaluates to a single value.

In both forms of CASE, if the optional ELSE clause is present, and if no matches are found, then the function returns the result in theELSE clause. If no ELSE clause is present, and no matches are found, then the result is NULL.

Usage notes¶

Note that, contrary to DECODE, a NULL value in the condition does not match a NULL value elsewhere in the condition. For example WHEN <null_expr> = NULL THEN 'Return me!' does not return “Return me!”. If you want to compare to NULL values, useIS NULL rather than = NULL.
The _condition#_, _expr_, _value_, and_result_ can all be general expressions and thus can include subqueries that include set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the General usage notes in theSet operators topic.

Collation details¶

In the first form of CASE, each expression is independent, and the collation specifications in different branches are independent. For example, in the following, the collation specifications in_condition1_ are independent of the collation specification(s) in _condition2_, and those collation specifications do not need to be identical or even compatible.

CASE WHEN THEN [ WHEN THEN ]

In the second form of CASE, although all collation-related operations must use compatible collation specifications, the collation specifications do not need to be identical. For example, in the following statement, the collation specifications of both _value1_ and _value2_ must be compatible with the collation specification of_expr_, but the collation specifications of _value1_ and _value2_ do not need to be identical to each other or to the collation specification of _expr_.

CASE WHEN THEN [ WHEN THEN ] ...

The value returned from the function has the highest-precedence collation of the THEN/ELSEarguments.

Examples¶

This example shows a typical use of CASE:

SELECT column1, CASE WHEN column1=1 THEN 'one' WHEN column1=2 THEN 'two' ELSE 'other' END AS result FROM (values(1),(2),(3)) v;

+---------+--------+ | COLUMN1 | RESULT | |---------+--------| | 1 | one | | 2 | two | | 3 | other | +---------+--------+

This example shows that if none of the values match, and there is no ELSE clause, then the value returned is NULL:

SELECT column1, CASE WHEN column1=1 THEN 'one' WHEN column1=2 THEN 'two' END AS result FROM (values(1),(2),(3)) v;

+---------+--------+ | COLUMN1 | RESULT | |---------+--------| | 1 | one | | 2 | two | | 3 | NULL | +---------+--------+

This example handles NULL explicitly.

SELECT column1, CASE WHEN column1 = 1 THEN 'one' WHEN column1 = 2 THEN 'two' WHEN column1 IS NULL THEN 'NULL' ELSE 'other' END AS result FROM VALUES (1), (2), (NULL);

+---------+--------+ | COLUMN1 | RESULT | |---------+--------| | 1 | one | | 2 | two | | NULL | NULL | +---------+--------+

The following examples combine CASE with collation:

SELECT CASE COLLATE('m', 'upper') WHEN 'M' THEN TRUE ELSE FALSE END;

END
True
+----------------------------+

SELECT CASE 'm' WHEN COLLATE('M', 'lower') THEN TRUE ELSE FALSE END;

END
True
+------------------------------------------+