Scalar Functions - 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

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 Advanced SQL Engine 16.20 Feature Update 1, but not Teradata Database 15.10, for example.)

The following table details a few examples exhibiting the above described behavior.

Statement Text SQL Generated by the ODBC Driver (i.e., SQLNativeSql ) Advanced SQL Engine 16.20 Feature Update 1 Teradata Database 15.10
SELECT {fn BIT_LENGTH('Teradata')}
SELECT(Octet_Length('Teradata')*8)
Succeeds Succeeds
SELECT BIT_LENGTH('Teradata')
SELECT BIT_LENGTH('Teradata')
Succeeds Fails
SELECT {fn DAYOFYEAR('2018-02-24') }
SELECT (((('2018-02-24')(DATE))-(((('2018-02-24')(DATE))/10000*10000+0101(DATE)))) + 1(TITLE 'DayOfYear()'))
Succeeds Succeeds
SELECT DAYOFYEAR('2018-02-24')
SELECT DAYOFYEAR('2018-02-24')
Fails Fails

The following functions were updated in Advanced SQL Engine to support the ODBC Driver-accepted syntax.

Function Call Change Advanced SQL Engine Version Implemented
CURRENT_DATE() Can be called with or without parenthesis. 16.20 Feature Update 1
LENGTH(s) Can also support numeric expressions. 16.20 Feature Update 1
LTRIM(s) Can also operate on numeric expressions. 16.20 Feature Update 1
RTRIM(s) Can also operate on numeric expressions. 16.20 Feature Update 1
SUBSTRING(s,n1[,n2]) New syntax in Teradata mode 16.20 Feature Update 1

Starting with Advanced SQL Engine 16.20 Feature Update 1, the following functions are supported.

Function Call Purpose
BIT_LENGTH(s) Returns the length in bits of the specified string.
CONCAT(s1,s2[,...sn]) Concatenates two or more parameter values into a single string.
CURDATE() Returns the current date.
CURTIME() Returns the current local time.
DAYOFMONTH(e) Returns the number of days from the beginning of the month to the specified date.
DAYOFWEEK(e) Returns the day of the week on which the specific date falls.
HOUR(e) Returns the hour field of the specified time expression.
LOCATE(s1,s2[,p]) Returns the position in s2 where the substring s1 starts.
MINUTE(e) Returns the minute field of the specified time expression.
MOD(x1, x2) Returns the remainder from a division.
MONTH(e) Returns the number of months from the beginning of the year to the specified date.
NOW() Returns the current date and time.
SECOND(e) Returns the second field of the specified time expression.
UCASE(s) Returns an equal string, which all lowercase characters convert to uppercase.
WEEK(e) Returns the number of weeks from the beginning of the year to the specified date.
YEAR(e) Returns the year of the specified date.