Usage Notes - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™
CALCMATRIX processes data representing a single input matrix or multiple input matrices. In the latter case, the input must have column(s) that identify which matrix a given input row belongs to. These columns are called as matrix id columns and the value of one tuple of matrix id columns is called a matrix id. All rows from the same matrix must have the same matrix id.
CALCMATRIX works in LOCAL and COMBINE phases.
  • In the LOCAL phase , the function processes the input data that exists in the source table on each amp separately and outputs rows of summarized data for that amp. No data has to be moved among amps.
  • In the COMBINE phase , the function processes the summarized data from each amp (that is, the output from the LOCAL phase) and combines it to form the rows that make up final output.

    In order to get one result matrix (or, if there are matrix id columns, one result matrix for each matrix id), the COMBINE phase must use a HASH BY clause and possibly a LOCAL ORDER BY clause.

LOCAL Phase Details

  • The input to the LOCAL phase must consist of zero or more matrix id columns, and 1 or more data columns (call this number n). The matrix id columns, if present, must be placed in the LOCAL ORDER BY list.
  • The output from the LOCAL phase consists of the matrix id columns, if any, an INT column named rownum, a VARCHAR(128) column named rowname, a BIGINT column named c (for count), a REAL column named s (for sum), and a REAL column for each data column of input.
  • The CALCTYPE and OUTPUT custom values in the LOCAL phase are always set to 'SSCP' and 'COLUMNS' respectively. Attempts to use different values for these keys are ignored.
  • If any of the data columns in a row are NULL, the row's handling depends on the setting for NULL_HANDLING.

COMBINE Phase Details

  • The input to the COMBINE phase must be exactly the columns that are output from the LOCAL phase: zero or more matrix id columns, an INT rownum column, a VARCHAR(128) rowname column, a BIGINT column named c (for count), a REAL column names s (for sum), and a REAL column for each data column of input.

    If there are matrix id columns in the data, they should be specified in the HASH BY and LOCAL ORDER BY lists. If not, a column with a constant value and an alias name, like "1 as p", should be added to the ON clause and the HASH BY list.

  • The custom values for CALCTYPE and OUTPUT control the output columns from the COMBINE phase.
  • If the OUTPUT is set to 'COLUMNS', then the columns output from the 'COMBINE' phase are: The matrix id columns, if present, an INT rownum column, a VARCHAR(128) rowname column, a REAL column for each data column of input and, if the CALCTYPE is 'ESSCP', a BIGINT column named c (for count), a REAL column named s (for sum).

    If the OUTPUT is set to 'VARBYTE', then the columns output from the 'COMBINE' phase are: an INT rowname column, a VARCHAR(128) rowname column, and a VARBYTE column named v.

  • If there are no matrix id columns, the final output of the COMBINE phase consists of n rows, where n is the number of data columns in the input. The rownum field has values from 1 to n. Thus each output row is one row from the resulting matrix. If there are matrix id columns, the final output of the COMBINE phase consists of n rows per matrix id. The rownum field will have values from 1 to n.

VARBYTE Output

When the OUTPUT is ‘VARBYTE’, the output row consists of the matrix id columns, if any, and these fields:
  • INT rownum
  • VARCHAR(128) rowname
  • VARBTYE value

Details for Both the LOCAL and the COMBINE Phases

  • The values for the rownum column ranges from 1 to n, the number of data columns in the input. If the rownum column has value x, the rowname column is the x'th data column name.
  • If the columns in the ON clause are expressions, they must have an alias name. Columns in the LOCAL ORDER BY or HASH BY lists must be column or alias names from the ON clause, or ordinal values.