Simple CASE
lets you return a result based on the value of a single input expression. You can define a default result if none of the comparison values match.
Note: there two forms of the CASE
statement: searched CASE
and simple CASE
. Searched CASE
is more powerful, while simple CASE
statements are simpler to construct.
Sample usage
Replace payment codes with intuitive names:
CASE Payment Type
WHEN "CC" THEN "Credit Card"
WHEN "DC" THEN "Debit Card"
WHEN "GC" THEN "Gift Card"
WHEN "CA" THEN "Cash"
ELSE "Other"
END
Syntax
CASE input_expression
WHEN expression_to_match THEN result
[WHEN expression_to_match THEN result]
[...]
[ELSE else_result]
END
Parameters
input_expression
The expression to evaluate. You can use any valid expression as the input_expression
.
WHEN expression_to_match
The WHEN
clause compares expression_to_match
to input_expression
and returns true if the two are equal, or false if they aren't. You can use any valid expression in the WHEN
clause.
THEN result
Each WHEN
clause must have a matching THEN
clause, which specifies the results to return if that condition is true. If there are multiple WHEN
clause, the CASE
expression returns the result for the first true clause. You can use any valid expression in the THEN
clause.
Notes
All of the THEN
clauses in a CASE
expression must return the same type of result.
For example, if the first THEN
clause returns Text, additional THEN clauses must also return Text.
ELSE else_result
The optional ELSE
clause specifies a default result. This is returned if none of the WHEN
clauses are true. If a CASE
expression has no ELSE
clause, and none of the WHEN
clauses are true, the statement returns NULL
. A CASE
expression can only have one ELSE
clause.
How simple CASE works
A simple CASE
expression consists of the following elements:
- The
CASE
keyword, followed by an input expression. WHEN
: the value against which to compare the input expression: if the value equals the input expression, then this clause is true. You can have multiple WHEN clauses in a single CASE expression.THEN
: the result to return if the WHEN clause's condition is true. You must have one THEN clause for each WHEN clause in your CASE expression.ELSE
: Optional. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause, orNULL
if no ELSE clause is specified.- The
END
keyword.
CASE
evaluates each successive WHEN
clause and returns the first result where the condition is true. Any remaining WHEN
clauses and the ELSE
result are not evaluated. If all WHEN
conditions are false or NULL
, CASE
returns the ELSE
result, or if no ELSE
clause is present, returns NULL
.
Example
Provide customized links for your premium customers:
CASE Premium Status
WHEN "Platinum" THEN CONCAT(Site URL, "platinum_welcome.html")
WHEN "Gold" THEN CONCAT(Site URL, "gold_welcome.html")
WHEN "Silver" THEN CONCAT(Site URL, "silver_welcome.html")
ELSE CONCAT(Site URL, "welcome.html")
END