Overview

Use case expressions to create a rule-based formula, where different cases have different outputs, similar to "if-else statements" in programming languages.

• case expressions can include any number of WHEN clauses and can optionally include an ELSE clause.
• If you omit the ELSE clause, cases where none of the WHEN conditions are met are filled with the value null.
• If the data satisfies multiple WHEN conditions, the output of the first WHEN statement is used.

You can write case expressions in two styles.

In the first style, you can use a simplified case expression in which a single attribute is directly compared with values present in the WHEN clauses. In this example, we populate the attribute company_code based on the contents of the attribute company_type.

If company_type is equal to the string 'Private', then company_code is populated with the string PRV. Similarly, a value of 'Public' in company_type results in a value of 'PUB' for company_code, and a value of 'Partnership' results in a value of 'PRT'. As we did not include the optional ELSE clause, company_code will be null when company_type does not match any of the options listed.

The second style of case expression is more flexible, allowing the use of multiple attributes in the WHEN clauses and more complex conditional statements, such as the array.contains() function. In the following example, we create a region column based on whether the value of the state column is within an array of states. We also ensure that the country column has the value 'USA' before applying these regional rules.

Nested Case Expressions

In some situations, it may be beneficial to use a case expression within another case expression. The following example of nested case expressions produces a result similar to the previous unnested example. There is one small difference in the result. Here we distinguish between records that are not in the USA, for which we assign the value 'NON-USA', and records that are within the USA, but do not fall within one of the defined regions, for which we assign the value 'UNKNOWN USA'.

📘

Case Expressions in Scripts

You can also include case expressions in scripts. To do this, use SELECT * before the beginning of your statement and include AS <target_attribute> after the END.