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;