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: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
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.
Syntax
CASE input_expression
WHEN expression_to_match THEN result
[WHEN expression_to_match THEN result]
[...]
[ELSE else_result]
ENDParameters
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 1 ELSE clause.
Another example
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
END