Handling Multiple Input Streams | SQL External Routine Programming | Vantage - Handling Multiple Input Streams - Analytics Database - Teradata Vantage

SQL External Routine Programming

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
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

You can read from multiple tables (input streams) inside table operators using multiple ON clauses. This allows table operators to be applied to related groups of information derived from different data sets. Data from these multiple data sources can be processed within a single SQL/MR function.

You can use the AS name clause to associate an optional alias with each input stream. To retrieve the alias name associated with an input stream, use the FNC_TblOpGetAsClauseName function.

The order of the ON clauses must match the stream numbers in the table operator implementation. The maximum number of input streams for a table operator is 16.

The ON clause can accept the following options:
  • No partitioning or order by attributes
  • PARTITION BY ANY
  • PARTITION BY ANY ORDER BY column_list
  • LOCAL ORDER BY column_list
  • PARTITION BY column_list
  • PARTITION BY column_list ORDER BY column_list
  • HASH BY column_list
  • HASH BY column_list LOCAL ORDER BY column_list
  • DIMENSION
  • DIMENSION ORDER BY column_list
The following rules apply:
  • You cannot specify ORDER BY as the only clause in the input table. It must be combined with a PARTITION BY [ANY] or DIMENSION clause.
  • You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.
You can use the following functions to access and set HASH BY, PARTITION BY and ORDER BY metadata.
  • FNC_TblOpGetHashByDef
  • FNC_TblOpGetCountHashByDef
  • FNC_TblOpGetLocalOrderByDef
  • FNC_TblOpGetCountLocalOrderByDef
  • FNC_TblOpSetHashByDef
  • FNC_TblOpSetLocalOrderByDef

To determine if the input to the table operator is DIMENSION input, use the FNC_TblOpIsDimension function.

For more information about the FNC functions that you can use with table operators, see Table Operator Interface.

For more information on using the ON, AS, HASH BY, PARTITION BY, ORDER BY, or DIMENSION clauses with table operators, see the information about SELECT in Teradata Vantageā„¢ - SQL Data Manipulation Language, B035-1146.