Handling Multiple Input Streams | SQL External Routine Programming | Vantage - 17.10 - Handling Multiple Input Streams - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
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
  • 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 Definition Language Syntax and Examples, B035-1144.