Simple CASE
returns a result based on the value of a single input expression, or a default result if none of the comparison values match.
Note: there are two forms of the CASE
statement: searched CASE
and simple CASE
. Searched CASE
statements allow you to use more sophisticated logic, while simple CASE
statements are simpler to construct.
See also: IF.
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. TheWHEN
clause comparesinput_expression
toinput_expression
and returns true if the two are equal, or false if they aren't.result
- Any valid field or expression. EachWHEN
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 the Text data type, additional THEN
clauses must also return the Text data type.
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 theinput_expression
: if the value equals theinput_expression
, then this clause is true. You can have multipleWHEN
clauses in a singleCASE
statement.THEN
: the result to return if theWHEN
clause's condition is true. You must have oneTHEN
clause for eachWHEN
clause in yourCASE
statement.ELSE
: Optional. If none of theWHEN
clause conditions are true,CASE
returns the value in theELSE
clause, orNULL
if noELSE
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