17.05 - Example: Function Processing with Input Table Variable Substitution - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

If an input table correlation name in the SELECT statement matches an IN TABLE substitution variable in the function mapping definition, the corresponding IN TABLE parameter name in the function mapping definition is substituted for function processing.

The function mapping definition usr_AllPairsShortestPath for the function AllPairsShortestPath includes IN TABLE variables for substitution.

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(GroupCol) ,
EdgeWeight(2) ,
MaxDistance(Distance) ,
Directed ,GroupSize ,SequenceInputBy;

This SELECT statement specifies the vertex and edge as table correlation names.

SELECT * FROM usr_AllPairsShortestPath (
ON callers AS vertex PARTITION BY callerid
ON calls AS edge PARTITION BY callerfrom
USING
TargetKey ('callerto')
EdgeWeight ('calls')
MaxDistance ('-1')
) as dt ORDER BY source, target;

The query is rewritten as follows during function processing. The table correlation name vertices is substituted for vertex and the table correlation name edges is substituted for edge.

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