Examples: Function Processing with Variable Substitution - 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™

Example: Single Value Specified for a Concatenated Variable Expression

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

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;