CASE statement

  • Article

Applies to: check marked yes Databricks Runtime 16.3 and later

Important

This feature is in Public Preview.

Executes thenStmtN for the first optN that equals expr or elseStmt if no optN matches expr. This is called a simple case statement.

Executes thenStmtN for the first condN evaluating to true, or elseStmt if no condN evaluates to true. This is called a searched case statement.

For case expressions that yield result values, see CASE expression

This statement may only be used within a compound statement.

Syntax

CASE expr
  { WHEN opt THEN { thenStmt ; } [...] } [...]
  [ ELSE { elseStmt ; } [...] ]
END CASE

CASE
  { WHEN cond THEN { thenStmt ; } [...] } [...]
  [ ELSE { elseStmt ; } [...] ]
END CASE

Parameters

  • expr: Any expression for which a comparison is defined.
  • opt: An expression with a least common type with expr and all other optN.
  • thenStmt: A SQL Statement to execute if preceding condition is true.
  • elseStmt: A SQL Statement to execute if no condition is true.
  • cond: A BOOLEAN expression.

Conditions are evaluated in order, and only the first set of stmt for which opt or cond evaluate to true will be executed.

Examples

-- a simple case statement
> BEGIN
    DECLARE choice INT DEFAULT 3;
    DECLARE result STRING;
    CASE choice
      WHEN 1 THEN
        VALUES ('one fish');
      WHEN 2 THEN
        VALUES ('two fish');
      WHEN 3 THEN
        VALUES ('red fish');
      WHEN 4 THEN
        VALUES ('blue fish');
      ELSE
        VALUES ('no fish');
    END CASE;
  END;
 red fish

-- A searched case statement
> BEGIN
    DECLARE choice DOUBLE DEFAULT 3.9;
    DECLARE result STRING;
    CASE
      WHEN choice < 2 THEN
        VALUES ('one fish');
      WHEN choice < 3 THEN
        VALUES ('two fish');
      WHEN choice < 4 THEN
        VALUES ('red fish');
      WHEN choice < 5 OR choice IS NULL THEN
        VALUES ('blue fish');
      ELSE
        VALUES ('no fish');
    END CASE;
  END;
 red fish