Example: Function Processing with a Concatenated Variable Expression - Analytics Database - Teradata Vantage

The function mapping definition usr_AllPairsShortestPath for the function AllPairsShortestPath specifies variables for input tables and parameters, including a concatenated variable expression for TargetKey.

CREATE FUNCTION MAPPING usr_AllPairsShortestPath
FOR AllPairsShortestPath SERVER TD_SERVER_DB.coprocessor
USING
vertices(vertex) IN TABLE ,
edges(edge) IN TABLE ,
sources(source) IN TABLE ,
targets(trgt) IN TABLE ,
TargetKey(GroupColumn||'_'||Distance) ,
EdgeWeight('calls') ,
MaxDistance(Distance) ,
Directed ,GroupSize ,SequenceInputBy;

The SELECT statement specifies a value for the variable Distance.

SELECT * FROM usr_AllPairsShortestPath (
ON callers AS vertices PARTITION BY callerid
ON calls AS edges PARTITION BY callerfrom
USING
Distance ('1')
) as dt ORDER BY source, target;

Upon submittal, the query is rewritten as follows. Per the mapping definition, MaxDistance is substituted for Distance. EdgeWeight is also included because the mapping definition specifies a default for this parameter. TargetKey is not included because the GroupColumn variable in the concatenated variable expression is not resolved.

SELECT * FROM AllPairsShortestPath (
ON callers AS vertices PARTITION BY callerid
ON calls AS edges PARTITION BY callerfrom
EdgeWeight ('calls')
MaxDistance ('1')
) ORDER BY source, target;