Scalar Functions - ODBC Driver for Teradata

Vantage supports a number of ODBC scalar functions, with additional scalar functions being added incrementally in new database releases. See Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145 for the list of the currently supported scalar functions by the database.

When a client queries a scalar function using a function escape sequence (using the ‘{fn scalar-function}’ syntax, for example, ‘SELECT {fn MOD(x, y) }’), ODBC Driver for Teradata will determine whether the connected database supports the scalar function. If so, ODBC Driver for Teradata will send the query directly to the database (e.g., “SELECT MOD(x, y)”); if not, the driver will transform the user query to a query that the connected database supports (e.g., “SELECT((x) MOD (y))”).

A client may send a scalar function query without the function escape sequence, for example, “SELECT MOD(x, y)”. In this case, ODBC Driver for Teradata will send the client query to the database as is without performing any kind of checks or transformation. This query will succeed if the connected database supports the scalar function, it will fail otherwise. (For the MOD function, it is supported by Teradata Database 16.20 Feature Update 1, but not Teradata Database 15.10, for example.)

The following table details a few examples exhibiting this described behavior.

SELECT {fn BIT_LENGTH('Teradata')}

SELECT(Octet_Length('Teradata')*8)

SELECT BIT_LENGTH('Teradata')

SELECT BIT_LENGTH('Teradata')

SELECT {fn DAYOFYEAR('2018-02-24') }

SELECT (((('2018-02-24')(DATE))-(((('2018-02-24')(DATE))/10000*10000+0101(DATE)))) + 1(TITLE 'DayOfYear()'))

SELECT DAYOFYEAR('2018-02-24')

SELECT DAYOFYEAR('2018-02-24')

The following functions were updated in Analytics Database to support the ODBC Driver-accepted syntax.

Starting with Teradata Database 16.20 Feature Update 1, the following functions are supported.