CALCMATRIX | SQL Operators & User-Defined Functions | Teradata Vantage - CALCMATRIX - 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™

Purpose

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

Syntax

[TD_SYSFNLIB.] CALCMATRIX (
  ON { tableName | ( query_expression ) }
  [ HASH BY hashByColList [,...] ]
  [ LOCAL ORDER BY localOrderByListSpec [,...] ]
  USING PHRASE ('value')
  [ CALCTYPE ('value') ]
  [ OUTPUT ('value') ]
  [ NULL_HANDLING ('value') ]
)
localOrderByListSpec
localOrderByList [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
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.