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.In this article:
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
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, or
NULLif 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.
WHEN expression_to_match THEN result
[WHEN expression_to_match THEN result]
The expression to evaluate. You can use any valid expression as the
The WHEN clause compares
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.
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.
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.
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 1 ELSE clause.
Replace one values with another value. For example, you can make your data easier to read by replacing codes with more intuitive names:
CASE Payment Type
WHEN "CC" THEN "Credit Card"
WHEN "D" THEN "Debit Card"
WHEN "GC" THEN "Gift Card"
WHEN "UNK" THEN "Unknown"
ELSE Payment Type