CASE statement
Applies to: Databricks Runtime 16.3 and later
Preview
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 withexpr
and all otheroptN
.thenStmt
: A SQL Statement to execute if preceding condition istrue
.elseStmt
: A SQL Statement to execute if no condition istrue
.cond
: ABOOLEAN
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
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