CALCMATRIX | SQL Operators & User-Defined Functions | Teradata Vantage - 17.10 - CALCMATRIX - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

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

ANSI Compliance

This statement is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

You must have EXECUTE FUNCTION privileges on the function or on the database containing the function.

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') ]
)

Syntax Elements

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.