CASE_N Function Syntax | Teradata Vantage - CASE_N Function Syntax - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
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.