Array (Teradata Database) - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.5
Published
February 2018
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
qhj1503087326201.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

The following table lists the available array functions, and a description of their purpose.

Embedded Services: Array Functions
Function Description
Array Constructor Constructs a new instance of an ARRAY type and initializes it using the ARRAY constructor method or function.
Array Element Reference Accesses the value of a specified element in an ARRAY data type value.
Array Scope Reference References one or more elements of an ARRAY data type when invoking an ARRAY function.
Vector Constructor Constructs a new instance of a VECTOR type and initializes it using the VECTOR constructor method or function.
ARRAY_AGG An aggregate function for use with ARRAY data types that allows arrays to be created as part of the SELECT list of a query.

See Expression With Order By (Teradata Database) for details about sorting the arguments of the ARRAY_AGG function.

ARRAY_COMPARE Performs a pairwise comparison of the elements of two arrays and returns a value of 1, 0, or NULL to indicate whether or not the two array arguments contain the same element values.
ARRAY_CONCAT Concatenates one-dimensional ARRAY data types and can be applied to a subset of the elements of the array.
ARRAY_GET Returns the element value in array_expr that corresponds to the specified index position.
ARRAY_UPDATE Updates all or a subset of the elements in array_expr to the specified new value.
ARRAY_UPDATE_STRIDE Updates all or a subset of the elements in array_expr to the specified new value. The stride argument indicates how many elements should be skipped between each updated element.
CARDINALITY Returns an integer representing the number of elements in an ARRAY data type that currently have assigned values, or the number of elements that are initialized within a specific scope reference.
Embedded Services: Array Aggregation Functions
Function Description
ARRAY_AVG Returns the average of all the element values in array_expr or the average of the elements within the specified scope.
ARRAY_COUNT_DISTINCT Returns the number of distinct elements in array_expr, optionally matching a specific input value.
ARRAY_MIN Returns the minimum of all element values in array_expr or the minimum of the elements within the specified scope.
ARRAY_MAX Returns the maximum of all element values in array_expr or the maximum of the elements within the specified scope.
ARRAY_SUM Returns a value representing the total sum of adding the values of each element in array_expr or the sum of the elements within the specified scope.
Embedded Services: Array Arithmetic Functions
Function Description
ARRAY_ADD Performs an add arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_SUB Performs a subtract arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_MUL Performs a multiply arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_DIV Performs a divide arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_MOD Performs a modular arithmetic operation on an ARRAY value, whether referred to in its entirety or over a scope reference.
Embedded Services: Array Comparison Functions
Function Description
ARRAY_GT Performs a greater than comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_GE Performs a greater than or equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_LT Performs a less than comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_LE Performs a less than or equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_EQ Performs an equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference.
ARRAY_NE Performs a not equal to comparison on the individual elements of an ARRAY value, whether referred to in its entirety or over a scope reference.
Embedded Services: Other Databases Functions
Function Description
OCOUNT Returns the number of elements in array_expr that contain either a NULL or non-NULL value.
ODELETE Removes all elements from array_expr.
OEXISTS Returns an integer value indicating whether the element specified by the index in array_expr contains a value or is in an uninitialized state.
OEXTEND Allocates space for one or more new elements in array_expr.
OFIRST Returns the lowest subscript value in array_expr as either an unsigned INTEGER value or a new instance of the predefined ARRAY type ArrayVec.
OLAST Returns the highest subscript value with a populated element in array_expr.
OLIMIT Returns the highest possible subscript value in array_expr as either an unsigned INTEGER value or a new instance of the predefined ARRAY type ArrayVec.
ONEXT Returns the subscript of the element subsequent to the element specified for array_expr, if that element is currently populated.
OPRIOR Returns the subscript of the element prior to the element specified for array_expr, if that element is currently populated.
OTRIM Removes one or more populated elements from the end of array_expr.
See the Teradata Database SQL Functions, Operators, Expressions, and Predicates document, B035-1145, for a detailed overview of these array functions.