Case()

Function reference
Tests the values of a column or expression and returns values based on the results of the test.

Case ( column WHEN value1 THEN result1 { WHEN value2 THEN result2 { … } } { ELSE resultelse } )

Argument Description
column The column or expression whose values you want to test.
WHEN Introduces a value-result pair. At least one WHEN is required.
value One or more values that you want to compare to values of column. A value can be:
  • A single value
  • A list of values separated by commas (for example, 2,4, 6, 8)
  • A TO clause (for example, 1 TO 20)
  • IS followed by a relational operator and comparison value (for example, IS>5)
  • Any combination of the above with an implied OR between expressions (for example, 1,3,5,7,9,27 TO 33,IS>42)
THEN Introduces the result to be returned when column matches the corresponding value.
result An expression whose value is returned by Case for the corresponding value. All result values must be the same datatype.
ELSE (optional) Specifies that for any values of column that do not match any of the values, already specified, Case returns result else.
resultelse An expression whose value is returned by Case when the value of column does not match any WHEN value expression.

Data type: The datatype of result
Returns the result you specify in result/resultelse.
If more than one WHEN clause matches column, Case returns the result of the first matching one.

Examples

  • Case( [MyField] WHEN ‘a’ THEN ‘Open’ WHEN ‘b’ THEN ‘Closed’ WHEN ‘c’ THEN ‘expired’ Else ‘Not valid!’)
  • Case( [MyField] WHEN IS >70000 THEN ‘High’ WHEN IS >50000 THEN ‘Medium’ ELSE ‘Low’)
Last modified: 23 May 2021