16.20 - Evaluation - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1145-162K
Language
English (United States)

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