17.05 - Examples: Function Processing with Variable Substitution - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

Example: Single Value Specified for a Concatenated Variable Expression

In the example below, MaxStep is substituted for Maxnum at function processing. However, ValueColumn is not submitted because AttributeValueColumn does not have a value.

This is the function mapping definition usr_SVMSparse for the function SparseSVMTrainer. The ValueColumn value consists of a concatenated variable expression that includes the variables AttributeValueColumn and Maxnum.

CREATE FUNCTION MAPPING usr_SVMSparse
FOR SparseSVMTrainer SERVER TD_SERVER_DB.coprocessor
USING
InputTable IN TABLE ,
ModelTable OUT TABLE ,
IDColumn ,
SampleIdColumn ,
AttributeColumn,
LabelColumn,
ValueColumn(AttributeValueColumn||'_'||Maxnum),
HashProjection , "Hash" , HashBuckets ,
Cost , Bias , ClassWeights ,
MaxStep(Maxnum) ,
Epsilon , Seed , SequenceInputBy
;

The SELECT statement specifies a value of 150 for Maxnum.

SELECT * FROM usr_SVMSparse (
ON svm_iris_input_train as InputTable
OUT TABLE ModelTable (svm_iris_model)
USING
SampleIDColumn ('id')
AttributeColumn ('attribute')
LabelColumn ('species')
Maxnum (150)
Seed ('0')
) as dt;

Upon submittal, the query is rewritten as follows for processing by the function SparseSVMTrainer. Per the function mapping definition, the variable MaxStep is substituted for Maxnum. ValueColumn is not submitted for processing because the SELECT statement did not specify a value for AttributeValueColumn.

SELECT * FROM SparseSVMTrainer (
ON svm_iris_input_train as InputTable
OUT TABLE ModelTable (svm_iris_model)
USING
SampleIDColumn ('id')
AttributeColumn ('attribute')
LabelColumn ('species')
MaxStep (150)
Seed ('0')
) as dt;

Example: Parameter with a Concatenated Variable Expression Submitted

The function mapping definition usr_SVMSparse includes the parameter ValueColumn with a concatenated variable expression consisting of AttributeValueColumn and Maxnum.

CREATE FUNCTION MAPPING usr_SVMSparse
FOR SparseSVMTrainer SERVER TD_SERVER_DB.coprocessor
USING
InputTable IN TABLE ,
ModelTable OUT TABLE ,
IDColumn ,
SampleIdColumn ,
AttributeColumn,
LabelColumn,
ValueColumn(AttributeValueColumn||'_'||Maxnum),
HashProjection , "Hash" , HashBuckets ,
Cost , Bias , ClassWeights ,
MaxStep(Maxnum) ,
Epsilon , Seed , SequenceInputBy
;

The SELECT statement specifies values for AttributeValueColumn and Maxnum.

SELECT * FROM usr_SVMSparse (
ON svm_iris_input_train as InputTable
OUT TABLE ModelTable (svm_iris_model)
USING
SampleIDColumn ('id')
AttributeColumn ('attribute')
LabelColumn ('species')
AttributeValuecolumn('value')
Maxnum (150)
Seed ('0')
) as dt;

Upon submittal, the query is rewritten as follows. The concatenated variable expression for ValueColumn resolves to value_150. Because the function mapping definition substitutes MaxStep for Maxnum, Maxstep is submitted with a value of 150.

SELECT * FROM SparseSVMTrainer (
ON svm_iris_input_train as InputTable
OUT TABLE ModelTable (svm_iris_model)
USING
SampleIDColumn ('id')
AttributeColumn ('attribute')
LabelColumn ('species')
Valuecolumn('value_150')
MaxStep (150)
Seed ('0')
) as dt;

Example: Scalar Subquery Variable Substitution

In the example below, the scalar subqueries in the SELECT statement are executed to provide values for AttributeValueColumn and Maxnum. Upon submittal, the query is rewritten to substitute the resolved values for ValueColumn and MaxStep.

The function mapping definition usr_SVMSparse includes the parameter ValueColumn with a concatenated variable expression consisting of AttributeValueColumn and Maxnum.

CREATE FUNCTION MAPPING usr_SVMSparse
FOR SparseSVMTrainer SERVER TD_SERVER_DB.coprocessor
USING
InputTable IN TABLE ,
ModelTable OUT TABLE ,
IDColumn ,
SampleIdColumn ,
AttributeColumn,
LabelColumn,
ValueColumn(AttributeValueColumn||'_'||Maxnum),
HashProjection , "Hash" , HashBuckets ,
Cost , Bias , ClassWeights ,
MaxStep(Maxnum) ,
Epsilon , Seed , SequenceInputBy
;

The SELECT statement specifies scalar subqueries for AttributeValuecolumn and Maxnum.

SELECT * FROM usr_SVMSparse (
ON svm_iris_input_train as InputTable
OUT TABLE ModelTable (svm_iris_model)
USING
SampleIDColumn ('id')
AttributeColumn ('attribute')
LabelColumn ('species')
AttributeValuecolumn(SELECT colvarch from ssqtbl WHERE username=CURRENT_USER)
Maxnum (SELECT colint FROM ssqtbl WHERE username=USER)
Seed ('0')
) as dt;

Upon submittal, the query is rewritten as follows for processing by the function SparseSVMTrainer. The scalar subquery for AttributeValueColumn resolves to value and the scalar subquery for Maxnum resolves to 1. The concatenated variable expression for ValueColumn resolves to value_1. Per the function mapping definition, MaxStep is substituted for Maxnum.

SELECT * FROM SparseSVMTrainer (
ON svm_iris_input_train as InputTable
OUT TABLE ModelTable ('svm_iris_model')
USING
SampleIDColumn ('id')
AttributeColumn ('attribute')
LabelColumn ('species')
Valuecolumn('value_1')
MaxStep (1)
Seed ('0')
) as dt;