Compatible Data Types | SQL External Routine Programming | Teradata Vantage - 17.10 - Compatible Types - 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)

The argument types passed in to a UDF when it appears in a DML statement do not always have to be an exact match with the corresponding parameter declarations in the function definition, but they must be compatible and follow the precedence rules that apply to compatible types.

For example, you can define a function that declares an INTEGER parameter and you can successfully call the function with a BYTEINT argument because BYTEINT and INTEGER are compatible types and BYTEINT can fit into an INTEGER.

You cannot successfully call the function with a FLOAT argument, however, even though FLOAT is compatible with INTEGER, because of the precedence rules that apply to compatible types. A FLOAT cannot fit into an INTEGER without a possible loss of information.

Data types within the following groups are compatible, and appear in the order of precedence. A data type has precedence over the other data types that follow it in the list of compatible types.

Group Compatible Type Precedence List
Character
  • CHAR
  • VARCHAR/CHAR VARYING/LONG VARCHAR
  • CLOB
Graphic
  • CHARACTER CHARACTER SET GRAPHIC
  • VARCHAR CHARACTER SET GRAPHIC or LONG VARCHAR CHARACTER SET GRAPHIC
Byte
  • BYTE
  • VARBYTE
  • BLOB
Numeric
  • BYTEINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • DECIMAL/NUMERIC
  • NUMBER
  • REAL/FLOAT/DOUBLE PRECISION

Data types separated by a slash ( / ) are synonyms and have the same precedence.

To define a function that accepts any numeric data type when you are not concerned with retaining exact precision, define the function parameter as REAL, FLOAT, or DOUBLE PRECISION. That way the function can accept any numeric data type because all numeric types are compatible and will be converted to the REAL data type before the function is called.

Data types that do not appear in the preceding table, such as DATE, TIME, and UDTs, are not compatible with any other data types.

To pass an argument that is not compatible with the corresponding parameter type, the function call must explicitly convert the argument to the proper type.

To provide a UDF that accepts argument types from different compatibility groups, you can do one of the following:
  • Use TD_ANYTYPE parameters, which can accept any system-defined data type or user-defined type (UDT). For more information, see Defining Functions that Use the TD_ANYTYPE Type.
  • Use function name overloading, which allows you to define more than one function that has the same name, but declares different parameter data types. For more information, see Function Name Overloading.