CASE_N Function Syntax | VantageCloud Lake - CASE_N Function Syntax - Teradata Vantage

CASE_N ( conditional_expression [,...] [, case_spec ] )

Syntax Elements

conditional_expression

An expression or comma-separated list of condition expressions to evaluate.

A conditional expression must evaluate to TRUE, FALSE, or UNKNOWN.

CASE_N evaluates conditional_expressions from left to right until a condition evaluates to TRUE or UNKNOWN, or until every condition evaluates to FALSE. The position of the first conditional_expression is one and the positions of subsequent conditions increment by one up to n, where n is the total number of conditional expressions.

Situation	Result
conditional_expression evaluates to TRUE, and all prior conditions evaluate to FALSE	CASE_N returns the position of the conditional_expression.
conditional_expression evaluates to UNKNOWN, and all prior conditions evaluate to FALSE	If NO CASE OR UNKNOWN is specified, CASE_N returns n + 1. If UNKNOWN is specified and NO CASE is not specified, CASE_N returns n + 1. If NO CASE and UNKNOWN are specified, CASE_N returns n + 2. If neither UNKNOWN nor NO CASE OR UNKNOWN is specified, CASE_N returns NULL.
Every conditional_expression evaluates to FALSE	If NO CASE or NO CASE OR UNKNOWN is specified, CASE_N returns n + 1. If neither NO CASE nor NO CASE OR UNKNOWN is specified, CASE_N returns NULL

case_spec

{ NO CASE [ { OR | , } UNKNOWN ] | UNKNOWN }

NO CASE

Evaluates to TRUE if every conditional_expression in the list evaluates to FALSE.

{ OR | , } UNKNOWN

The NO CASE OR UNKNOWN condition evaluates to TRUE if every conditional_expression in the list evaluates to FALSE, or if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE.

UNKNOWN

Evaluates to TRUE if a conditional_expression evaluates to UNKNOWN and all prior conditions in the list evaluate to FALSE.