case expression

  • Article

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns resN for the first optN that equals expr or def if none matches.

Returns resN for the first condN evaluating to true, or def if none found.

Syntax

CASE expr {WHEN opt1 THEN res1} [...] [ELSE def] END

CASE {WHEN cond1 THEN res1} [...] [ELSE def] END

Arguments

  • expr: Any expression for which comparison is defined.
  • optN: An expression that has a least common type with expr and all other optN.
  • resN: Any expression that has a least common type with all other resN and def.
  • def: An optional expression that has a least common type with all resN.
  • condN: A BOOLEAN expression.

Returns

The result type matches the least common type of resN and def.

If def is omitted the default is NULL. Conditions are evaluated in order and only the resN or def which yields the result is executed.

Examples

> SELECT CASE WHEN 1 > 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
 1.0
> SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 > 0 THEN 2.0 ELSE 1.2 END;
 2.0
> SELECT CASE WHEN 1 < 0 THEN 1 WHEN 2 < 0 THEN 2.0 END;
 NULL
> SELECT CASE 3 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END;
 C