CASE (simple)

Evaluates a condition and returns the first matching result expression.

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 NULL 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.

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 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

Was this helpful?
How can we improve it?
true
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false