17.10 - Example: Viewing Results for UNKNOWN Conditions - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Functions, Expressions, and Predicates

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

In this example, the session collation is ASCII.

CASE_N (a<'b', a>='ba' and a<'dogg' and b<>'cow', c<>'boy', UNKNOWN)

The following table shows the result value returned by the above CASE_N function given the specified values for a, b, and c. The x and yrepresent any value or NULL. The value 4 is returned if a condition is UNKNOWN with all preceding conditions evaluating to FALSE. NULL is returned if all the conditions are false.

a b c Result
'a' x y 1
'boy' 'girl' y 2
'boy' NULL y 4
'boy' 'cow' 'man' 3
'boy' 'cow' 'boy' NULL
'dog' 'ball' y 2
'dogg' x NULL 4
'dogg' x 'man' 3
'egg' NULL 'boy' NULL
'egg' x 'boy' NULL
'egg' x NULL 4
'egg' x 'girl' 3