Compatible Data Types | SQL External Routine Programming | Teradata Vantage - Compatible Types - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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: