Function Mapping and the USING Clause - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

Values that you specify for parameters in the USING clause of a function mapping override the default values in the function mapping definition.

Arguments that are defined with values in the mapping definition, but not specified in the USING custom clause, are appended by the system when the function mapping is executed. If you specify default values in the mapping definition, those default values do not have to be specified in the function mapping execution.

In this scenario, the function mapping definition specifies a default clicklag value of 20.

CREATE FUNCTION MAPPING appl_view_db.sessionize 
	FOR sessionize SERVER coprocessor 
	USING 
	    Timecolumn, timeout(100), clicklag(20), emitnull;

Because the SELECT statement below specifies the clicklag value as 200, the function mapping execution uses the clicklag value of 200 instead of the clicklag value of 20 specified in the function mapping definition.

SELECT * FROM test_db.sessionize ( 
   	   ON test_user.t1 AS InputTable PARTITION BY 1
   	   USING
      	Timecolumn('Test')
      	clicklag(200)
	) AS dt;

This SELECT statement below does not specify the emitnull parameter and because the mapping definition does not include a default value for emitnull, the emitnull parameter is not passed to the remote system during execution.

SELECT * FROM test_db.sessionize ( 
   	   ON test_user.t1 AS InputTable PARTITION BY 1
   	   USING
      	Timeout(10)
      	Timecolumn('Test')
      	clicklag(200)
	) AS dt;

Although the SELECT statement below does not include the clicklag and timeout parameters, the function mapping definition specifies the clicklag and timeout parameters with default values.

SELECT * FROM test_db.sessionize ( 
   	   ON test_user.t1 AS InputTable PARTITION BY 1
   	   USING
      	Timecolumn('Test')
	) AS dt;

The SELECT statement function mapping must provide all parameters required by remote system to execute the function. Otherwise, an error occurs.

To execute the Sessionize function, for example, the Timecolumn parameter is mandatory. Because the function mapping definition below does not include a default value for Timecolumn, you must specify a Timecolumn value in the SELECT statement function mapping.

CREATE FUNCTION MAPPING appl_view_db.sessionize 
   FOR sessionize SERVER coprocessor 
   USING 
  Timecolumn, Timeout, clicklag(20), emitnull;

The SELECT statement below specifies a Timecolumn value for the function mapping.

SELECT * FROM test_db.sessionize ( 
   ON test_user.t1 AS InputTable PARTITION BY 1
   USING
      Timeout(10)
      Timecolumn('Test')
      clicklag(20)
) AS dt;