The new lightweight parser transforms the ODBC Scalar functions within the ODBC Escape Sequence syntax to the Teradata Database specific functions. The ODBC Escape Sequence for calling a scalar function is: {fn scalar-function}.
The new lightweight parser ignores ODBC scalar functions outside of the ODBC escape sequence (i.e., scalar-function vs. {fn scalar-function}) and the query is passed as is to the Teradata Database. The query executes successfully if the Teradata Database supports the ODBC scalar function; otherwise the query produces an error. Please refer to the Teradata Database Functions, Operators, Expressions and Predicates User Guide (B035-1445) for the list of the Teradata Database supported scalar functions.
The following table details a few examples exhibiting the above described behavior.
Statement Text | SQL Generated by the Lightweight Parser (i.e., SQLNativeSql ) | Teradata Database 16.0 | Teradata Database 13.0 |
---|---|---|---|
select { fn LTRIM (' Teradata') } |
select TRIM( LEADING FROM ' Teradata' ) |
Succeeds | Succeeds |
select LTRIM (' Teradata') |
select LTRIM(' Teradata') |
Succeeds | Fails |
select { fn LTRIM (' Teradata') }, LTRIM (' Teradata') |
select TRIM( LEADING FROM ' Teradata' ), LTRIM(' Teradata') |
Succeeds | Fails |
select {fn BIT_LENGTH('Teradata')} |
SELECT(Octet_Length('Teradata')*8) |
Succeeds | Succeeds |
select BIT_LENGTH('Teradata') |
select BIT_LENGTH('Teradata') |
Fails | Fails |
The ODBC scalar functions shown in the following should only be used with escape sequences. Scalar functions could be used with or without escape sequences in previous versions; however, this feature has been deprecated. For more information, see ODBC Scalar Functions Outside Escape Sequences (deprecated in 14.10).
StringFunctions | ||
CONCAT() | LEFT() | LENGTH() See (1) at end of table |
LOCATE() | LTRIM() | RTRIM() |
SUBSTRING() | UCASE() | BIT_LENGTH() |
OCTET_LENGTH() | POSITION() | CHARACTER_LENGTH() |
CHAR_LENGTH() | RIGHT() | |
Numeric Functions | ||
ABS() | EXP() | LOG() |
MOD() | PI() | SQRT() |
Time and Date Functions | ||
CURDATE() | CURTIME() | DAYOFMONTH() |
CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
DAYOFWEEK() | DAYOFYEAR() | HOUR() |
MINUTE() | MONTH() | MONTHNAME() |
NOW() | QUARTER() | SECOND() |
YEAR() | WEEK() | TIMESTAMPADD() |
TIMESTAMPDIFF() | EXTRACT() | |
System Functions | ||
USER() | ||
Cursor Functions | ||
CONVERT() | ||
(1) When dealing with Kanji data, the string function LENGTH() returns a count of logical characters and not physical bytes. |