Print      Open PDF Version of Online Help

Analytics > Designing an Analysis > Using Functions in Analyses > Case Statements

Case Statements

Case statements are building blocks for creating conditional expressions that can produce different results for different column values.

Case (Switch)

This form of the Case statement is also referred to as the CASE (Lookup) form. The value of expression1 is examined, then the WHEN expressions. If expression1 matches any WHEN expression, it assigns the value in the corresponding THEN expression.

If expression1 matches an expression in more than one WHEN clause, only the expression following the first match is assigned.

If none of the WHEN expressions match, it assigns the value specified in the ELSE expression. If no ELSE expression is specified, it assigns the value NULL.

Refer also to the Case (If) statement.

Syntax:

CASE expression1

WHEN expression THEN expression

{WHEN expression... THEN expression...}

ELSE expression

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 the above example, the WHEN statements must reflect a strict equality; 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, it assigns the value NULL.

Refer also to the Case (Switch) statement.

Syntax:

CASE

WHEN request_condition1 THEN expression1

{WHEN request_condition2 THEN expression2}

{WHEN request_condition... THEN expression...}

ELSE expression

END

Where:

CASE

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

WHEN

Specifies the condition to be satisfied.

THEN

The value to assign if the corresponding WHEN expression is satisfied.

ELSE

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

END

Ends the CASE statement.

Example

CASE

WHEN score-par < 0 THEN 'Under Par'

WHEN score-par = 0 THEN 'Par'

WHEN score-par = 1 THEN 'Bogie'

WHEN score-par = 2 THEN 'Double Bogey'

ELSE 'Triple Bogey or Worse'

END

Unlike the Switch form of the CASE statement, the WHEN statements in the If form allow comparison operators; a WHEN condition of

WHEN < 0 THEN 'Under Par'

is legal.