16.20 - CALCMATRIX - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

Purpose

Calculates a sum-of-squares-and-cross-products (SSCP) matrix.

Syntax



TD_SYSFNLIB.
Name of the database where the function is located.
tableName
The name of the table with the input data for the function.
query_expression
The SELECT statement with input data for the function.
hashByColList
Specifies the column name set by which rows are to be hashed across the AMPs before being passed to the operator.
localOrderByList
Specifies the column name set by which rows are to be value-ordered locally on the AMPs before being passed to the operator.
ASC
Results are to be ordered in ascending sort order.
If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.
The default order is ASC.
DESC
Results are to be ordered in descending sort order.
If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.
NULLS FIRST
NULL results are to be listed first.
NULLS LAST
NULL results are to be listed last.
PHASE
Specifies one of the phases of the function. Valid values:
  • ‘LOCAL’
  • ‘COMBINE’
For a definition of LOCAL and COMBINE, see Usage Notes.
CALCTYPE
Specifies the type of matrix CALCMATRIX creates. Valid values:
  • ‘SSCP’ = sum-of-squares-and-cross-products matrix (the default)
  • ‘ESSCP’= Extended-sum-of-squares-and-cross-products matrix
  • ‘CSSCP’ = Corrected-sum-of-squares-and-cross-products matrix
  • ‘COV’ = Covariance matrix
  • 'COR' = Correlation matrix
OUTPUT
Specifies the format of the output. Valid values:
  • ‘COLUMNS’
  • ‘VARBYTE’
NULL_HANDLING
Specifies how nulls that appear as in any data columns are handled. Valid values:
  • ‘ZERO’ = any NULL values are considered to be 0, and the row is processed (default).
  • ‘IGNORE’ = the row is skipped.