Conditional Expressions as a Collection of Logical Primitives - 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™

You can think of a conditional expression as a collection of logical predicate primitives where the order of evaluation is controlled by the use of the logical operators AND, OR, and NOT and by the placement of parentheses.

Superficially similar conditional expressions can produce radically different results depending on how you group their component primitives, so use caution in planning the logic of any conditional expressions.

SQL supports the logical predicate primitives listed in the following table. Note that Match and Unique conditions are not supported.

Logical Predicate Primitive Condition SQL Logical Predicate Function
Comparison For a complete list of SQL comparison operators, see Supported Comparison Operators. Tests for equality, inequality, or magnitude difference between two data values.
Range BETWEEN

NOT BETWEEN

Tests whether a data value is included within (or excluded from) a specified range of column data values.
Like LIKE Tests for a pattern match between a specified character string and a column data value.
In IN

NOT IN

Tests whether a data value is (or is not) a member of a specified set of column values.

IN is equivalent to = ANY.

NOT IN is equivalent to <> ALL.

All ALL Tests whether a data value compares TRUE to all column values in a specified set.
Any ANY
SOME Tests whether a data value compares TRUE to any column value in a specified set.
Exists EXISTS

NOT EXISTS

Tests whether a specified table contains at least one row.
Period predicates For a complete list of period predicate operators, see "Period Functions and Operators" in Teradata Vantage™ - Data Types and Literals, B035-1143. Operates on:
  • Two Period expressions
  • Two derived periods
  • One Period expression and one derived period
  • One Period expression and one DateTime expression

Evaluates to TRUE, FALSE, or UNKNOWN.

OVERLAPS Tests whether two time periods, including derived periods, overlap
IS UNTIL_CHANGED

IS NOT UNTIL_CHANGED

Tests whether the ending bound of a Period expression, including a derived period is (or is not) UNTIL_CHANGED.