16.20 - Handling Multiple Input Streams - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
April 2020
Content Type
Programming Reference
Publication ID
English (United States)

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
  • 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 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 SELECT Statement” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.