16.20 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Before calling FNC_GetDistinctValue, you must allocate the buffer pointed to by returnValue using the C data type that maps to the underlying type of the distinct UDT. For example, if the distinct type represents an SQL INTEGER data type, declare the buffer like this:

INTEGER value;

If the underlying type of the distinct UDT is a character string, FNC_GetDistinctValue returns a null-terminated character string. The buffer you define must be large enough to accommodate null termination.

For information on the C data types that you can use, see C Data Types.

To guarantee that the value you pass in for the bufSize argument matches the length of the data type in Teradata Database, use the following macros defined in the sqltypes_td.h header file.

Macro Description
SIZEOF_CHARACTER_LATIN_WITH_NULL(len)
SIZEOF_CHARACTER_KANJISJIS_WITH_NULL(len)
SIZEOF_CHARACTER_KANJI1_WITH_NULL(len)
SIZEOF_CHARACTER_UNICODE_WITH_NULL(len)
Returns the length in bytes of a CHARACTER data type of len characters, including null termination characters. For example, the following returns a length of 8 (3 * 2 + 2 = 8):
SIZEOF_CHARACTER_UNICODE_WITH_NULL(3)
SIZEOF_VARCHAR_LATIN_WITH_NULL(len)
SIZEOF_VARCHAR_KANJISJIS_WITH_NULL(len)
SIZEOF_VARCHAR_KANJI1_WITH_NULL(len)
SIZEOF_VARCHAR_UNICODE_WITH_NULL(len)
Returns the length in bytes of a VARCHAR data type of len characters, including null termination characters. For example, the following returns a length of 8 (3 * 2 + 2 = 8):
SIZEOF_VARCHAR_UNICODE_WITH_NULL(3)
SIZEOF_BYTE(len)
SIZEOF_VARBYTE(len)
Returns the length in bytes of the specified BYTE or VARBYTE data type, where len specifies the number of values.
SIZEOF_GRAPHIC(len)
SIZEOF_VARGRAPHIC(len)
Returns the length in bytes of the specified CHARACTER(n) CHARACTER SET GRAPHIC or VARCHAR(n) CHARACTER SET GRAPHIC data type, where len specifies the number of values.
SIZEOF_BYTEINT
SIZEOF_SMALLINT
SIZEOF_INTEGER
SIZEOF_BIGINT
SIZEOF_REAL
SIZEOF_DOUBLE_PRECISION
SIZEOF_FLOAT
SIZEOF_DECIMAL1
SIZEOF_DECIMAL2
SIZEOF_DECIMAL4
SIZEOF_DECIMAL8
SIZEOF_DECIMAL16
SIZEOF_NUMERIC1
SIZEOF_NUMERIC2
SIZEOF_NUMERIC4
SIZEOF_NUMERIC8
SIZEOF_NUMERIC16
SIZEOF_NUMBER
Returns the length in bytes of the specified numeric data type.

For NUMBER, the length returned is 4 + 2 + 17 = 23 bytes since Teradata Database allocates max length (17 bytes) for the mantissa.

SIZEOF_DATE
SIZEOF_ANSI_Time
SIZEOF_ANSI_Time_WZone
SIZEOF_TimeStamp
SIZEOF_TimeStamp_WZone
Returns the length in bytes of the specified DateTime type.
SIZEOF_INTERVAL_YEAR
SIZEOF_IntrvlYtoM
SIZEOF_INTERVAL_MONTH
SIZEOF_INTERVAL_DAY
SIZEOF_IntrvlDtoH
SIZEOF_IntrvlDtoM
SIZEOF_IntrvlDtoS
SIZEOF_HOUR
SIZEOF_IntrvlHtoM
SIZEOF_IntrvlHtoS
SIZEOF_MINUTE
SIZEOF_IntrvlMtoS
SIZEOF_IntrvlSec
Returns the length in bytes of the specified Interval type.