Function Mapping and the USING Clause - 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 run. 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 run the function. Otherwise, an error occurs.

To run the Sessionize function, for example, the Timecolumn parameter is required. 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;