IN-List Expansion (deprecated in 16.20) - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

Product
ODBC Driver for Teradata
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-08-22
dita:mapPath
xbl1544831938754.ditamap
dita:ditavalPath
nkw1500504256726.ditaval
dita:id
B035-2526
lifecycle
previous
Product Category
Teradata Tools and Utilities

The parser transforms an IN-list containing ODBC Date, Time, Timestamp, or Interval literal escapes into a sequence of comparisons to work around a limitation in the database where only constants and not expressions are allowed in the in-list. This transformation is deprecated in 16.20.

For example, in Integer Date mode the ODBC Date literal {d '2008-11-28'} is expanded to:

('2008-11-28' (DATE, FORMAT 'YYYY-MM-DD'))

The resulting expansion is regarded as an expression by the database and is not allowed in the IN-list. The ODBC parser therefore transforms a construct like:

... x IN ({d '2008-11-28'}, {d '2009-12-29'})

into:

... (x = ('2008-11-28' (DATE, FORMAT 'YYYY-MM-DD'))

Or

x = ('2009-12-29' (DATE, FORMAT 'YYYY-MM-DD')))

With the new parser the ODBC date literal escape sequence is expanded as DATE 'YYYY-MM-DD'. For example in Integer Date mode the ODBC Date literal {d '2008-11-28'} is expanded to: DATE '2008-11-28'. New parser in-list expansion of Integer-Date works fine, but does not work in the case of Integer-Time, because the resulting Native-Syntax is an expression. The workaround is to switch to ANSI time.