17.10 - Behavior When Using NULL as a Literal Argument - 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)

To specify whether Vantage invokes a Java method when an SQL statement passes NULL as a UDF argument, the CREATE FUNCTION or REPLACE FUNCTION statement can include the RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUTclause.

IF an input argument is the NULL keyword and the corresponding CREATE FUNCTION statement … THEN …
specifies RETURNS NULL ON NULL INPUT the method is not evaluated and the result is always NULL.
specifies CALLED ON NULL INPUT the method is passed a null input argument.
does not specify either clause

To properly handle the NULL literal as an input argument, a Java UDF cannot use the default mapping convention if the SQL data types in the parameter list of the CREATE FUNCTION or REPLACE FUNCTION statement map to Java primitives.

To override the default mapping and map SQL data types to Java classes that can handle the NULL literal as an input argument, the EXTERNAL NAME clause in the CREATE FUNCTION or REPLACE FUNCTION statement must explicitly specify the mapping in the parameter list of the Java method.

For details on how SQL data types map to Java data types, see SQL Data Type Mapping. For an example that shows how to override the default mapping, see Example: Overriding Default Parameter Mapping to Handle NULLs.