CASE

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

The CASE expression is a generic conditional expression, similar to if/else statements in other programming languages. Use a CASE expression wherever an expression is valid.

CASE has two variants.

1CASE
2    WHEN <condition> THEN <result>
3    [WHEN ...]
4    [ELSE <result>]
5END
6
7CASE <expression>
8    WHEN <value> THEN <result>
9    [WHEN ...]
10    [ELSE <result>]
11END

In the first variant:

• <condition>: An expression that returns a boolean result. The <condition> of each WHEN clause is evaluated in order until one returns true.

In the second variant:

• <expression>: An expression that’s computed once and then compared to the <value> of each WHEN clause.
• <value>: A value to compare against <expression>.

In both variants:

• <result>: The value returned when the associated WHEN clause matches.

• ELSE <result>: The value returned when no WHEN clause matches. If the ELSE clause is omitted and no clause matches, then the result is NULL.

Returns the <result> of the first matching WHEN clause. If no clause matches, returns the ELSE result, or NULL if there’s no ELSE clause.

In the first variant, if a condition returns true, the value of the CASE expression is the <result> that follows the condition, and the rest of the expression isn’t processed. If the condition isn’t true, the expression examines subsequent WHEN clauses in the same manner.

In the second variant, called a simple CASE expression, <expression> is computed and then compared to each <value> until an equal value is found.

• A CASE expression evaluates only the subexpressions that are required to determine the result. For example, this CASE expression is a potential way to avoid a division-by-zero failure:

  1SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

• The data types of all <result> expressions must be convertible to a single output type.

• In a simple CASE expression, a NULL input never equals any WHEN value, so the expression returns the ELSE result, or NULL if there’s no ELSE clause.

These examples use this test table:

1a
2--
31
42
53

Use the first variant to map each value to a label.

1SELECT a,
2    CASE
3        WHEN a=1 THEN 'one'
4        WHEN a=2 THEN 'two'
5        ELSE 'other'
6    END
7FROM test;

Returns the result:

1a | case
2--+-------
31 | one
42 | two
53 | other

Use the simple CASE expression to write the previous example more concisely.

1SELECT a,
2    CASE a
3        WHEN 1 THEN 'one'
4        WHEN 2 THEN 'two'
5        ELSE 'other'
6    END
7FROM test;

Returns the same result.

• COALESCE
• NULLIF
• Conditional Expressions