16.10 - ON Clause Usage Notes - Teradata Database

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K
  • If a query is provided as part of the ON clause, it must be enclosed in parentheses.
  • PARTITION BY ANY, HASH BY, and LOCAL ORDER BY clauses are not supported.
  • There must be at least one PARTITION BY or PARTITION BY ORDER BY clause provided for one input stream. The remaining input streams can have the following combinations of PARTITION BY, ORDER BY, and DIMENSION clauses:
    • PARTITION BY ORDER BY
    • PARTITION BY
    • DIMENSION
    • DIMENSION ORDER BY
  • If there are multiple PARTITION BY clauses, the number and type of the partitioning columns must match and the data types should be compatible.
  • You can only use DIMENSION input when there are multiple inputs. There must be at least one other PARTITION BY clause or PARTITION BY ORDER BY clause used with DIMENSION.
  • The Teradata PARTITION BY clause allows you to specify the column position instead of the column name, which determines how the rows are partitioned.
  • The maximum number of input fields in the ON clause SELECT list is 2048. NPATH includes the symbol predicates, RESULT clauses, and ORDER BY clauses in the input SELECT list, so the total count of all these input fields should not exceed 2048.
  • The ON clause input table can have an optional alias associated with it using the AS name clause.