Conditional Expressions in Analytics

Expressions are building blocks for creating conditional expressions that convert a
value from one form to another.

CASE (Switch)

This form of the CASE statement is also referred to as the CASE(Lookup) form. The
value of expr1 is examined, then the WHEN expressions. If expr1 matches any
WHEN expression, it assigns the value in the corresponding THEN expression.
If none of the WHEN expressions match, it assigns the default value specified in the
ELSE expression. If no ELSE expression is specified, the system automatically adds
an ELSE NULL.

If expr1 matches an expression in multiple WHEN clauses, only the expression following
the first match is assigned.

note: In a CASE statement, AND has precedence over OR.

Syntax

CASE expr1

WHEN expr2 THEN expr3
{WHEN expr… THEN expr…}
ELSE expr

END

Where

CASE starts the CASE statement. Must be followed by an expression
and one or more WHEN and THEN statements, an optional ELSE
statement, and the END keyword.

WHEN specifies the condition to be satisfied.

THEN specifies the value to assign if the corresponding WHEN expression
is satisfied.

ELSE specifies the value to assign if none of the WHEN conditions are
satisfied. If omitted, ELSE NULL is assumed.

END ends the CASE statement.

Example

CASE Score-par

WHEN -5 THEN ‘Birdie on Par 6’
WHEN -4 THEN ‘Must be Tiger’
WHEN -3 THEN ‘Three under par’
WHEN -2 THEN ‘Two under par’
WHEN -1 THEN ‘Birdie’
WHEN 0 THEN ‘Par’
WHEN 1 THEN ‘Bogey’
WHEN 2 THEN ‘Double Bogey’
ELSE ‘Triple Bogey or Worse’

END

In this example, the WHEN statements must reflect a strict equality. For example, a
WHEN condition of WHEN < 0 THEN ‘Under Par’ is illegal because comparison operators
are not allowed.

CASE (If)

This form of the CASE statement evaluates each WHEN condition and if satisfied,
assigns the value in the corresponding THEN expression.
If none of the WHEN conditions are satisfied, it assigns the default value specified
in the ELSE expression. If no ELSE expression is specified, the system automatically
adds an ELSE NULL.

Syntax

CASE

WHEN request_condition1 THEN expr1
{WHEN request_condition2 THEN expr2}
{WHEN request_condition… THEN expr…}
ELSE expr

END