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.
- 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
- 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.
- 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.