CASE_N Function Syntax | Teradata Vantage - CASE_N Function Syntax - Advanced SQL Engine - Teradata Database

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
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.
IF … THEN …
a conditional_expression evaluates to TRUE, and all prior conditions evaluate to FALSE CASE_N returns the position of the conditional_expression.
a 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.