Example: Function Processing with a Scalar Subquery - Teradata VantageCloud Lake

The function mapping definition usr_AllPairsShortestPath for the function AllPairsShortestPath specifies TargetKey as a concatenated variable expression consisting of GroupColumn and Distance. The default value for Distance is derived from the scalar subquery:

SELECT distnum FROM ssqtbl WHERE username=CURRENT_USER

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) , Distance(SELECT distnum FROM ssqtbl WHERE username=CURRENT_USER),
Directed ,GroupSize ,SequenceInputBy;

This SELECT statement specifies a scalar subquery for GroupColumn. The value for distnum is 5 and tabname is tabl1 in the scalar subquery table ssqtbl for the CURRENT logged-on user.

SELECT * FROM usr_AllPairsShortestPath (
ON callers AS vertices PARTITION BY callerid
ON calls AS edges PARTITION BY callerfrom
USING
GroupColumn(SELECT tabname FROM ssqtbl WHERE username=CURRENT_USER)
) as dt ORDER BY source, target;

On submittal, the query is rewritten as follows. The function mapping definition specifies a concatenated expression for TargetKey, which resolves to tabl_5. The Distance scalar subquery resolves to 5. MaxDistance is substituted for Distance.

SELECT * FROM AllPairsShortestPath (
ON callers AS vertices PARTITION BY callerid
ON calls AS edges PARTITION BY callerfrom
USING
TargetKey('tabl_5')
MaxDistance('5')
EdgeWeight('calls')
) as dt ORDER BY source, target;