CALCMATRIX Syntax - Teradata Vantage

[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 CALCMATRIX 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.