Skip to main content
Last updated on

CASE statement

Applies to: check marked yes Databricks Runtime 16.3 and above

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​

SQL
-- 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