case

Description

The resulting value will be based on a range of conditions, which are defined on separate case...when lines. Using else, describe what value should be assigned when none of the previous conditions is met. The expression has to end with the keyword end.

This operator is often used for calculated information.

Syntax

case
when [condition_1] then [expression_1]
...
when [condition_n] then [expression_n]
else [else_expression]
end
  • all conditions needs to evaluate to either true or false. Examples could be a comparison (p.age >= 18) or a yes/no question (p.has_insurance).

  • All expressions needs to return the same type of value, e.g. a number.

Example

case
when
p.aum < 100000 then low
when
p.aum < 200000 then mid
else high
end

Here, several ranges are defined for how to qualify a person's total value in assets under management (aum). This can impact the services offered to the client.

When the value is under 100.000, the qualifier 'low' is attached to it. For values between 100.000 and 200.000, the qualifier is 'mid'. Else, meaning: every other value of 200.000 and over, the qualifier is 'high'.

Feel free to add as many steps between the low and high extreme as needed.