Function Mapping and the USING Clause - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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 following SELECT statement 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;

The following SELECT statement 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 following SELECT statement 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 following function mapping definition 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 following SELECT statement 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;