Share via

CASE statement

Applies to: check marked yes Databricks Runtime 16.3 and above

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 the preceding condition is true.

  • elseStmt

    A SQL statement to execute if no cond is true. That is all cond are false or null.

  • cond

    A BOOLEAN expression.

Conditions are evaluated in order, and only the first set of statements for which opt or cond evaluates 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