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
- Any valid field or expression.expression_to_match
- Any valid field or expression.The
WHEN
clause comparesexpression_to_match
toinput_expression
and returns true if the two are equal, or false if they aren't.result
- Any valid field or expression.Each
WHEN
clause must have a matchingTHEN
clause, which specifies the results to return if that condition is true. If there are multipleWHEN
clauses, theCASE
statement returns the result for the first true clause.else_result
(optional) - Any valid field or expression. TheELSE
else_result
clause specifies a default result for theCASE
statement. This clause is returned if none of theWHEN
clauses are true. If aCASE
statement has noELSE
clause, and none of theWHEN
clauses are true, theCASE
statement returnsNULL
.
Note: All of the THEN
clauses in a CASE
statement must return the same type of result. For example, if the first THEN
clause returns Text, additional THEN
clauses must also return Text.
How simple CASE works
A simple CASE
statement 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