17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
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, 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 Vantage 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.