Usage Notes

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

For the arithmetic functions ARRAY_ADD, ARRAY_SUB, ARRAY_MUL, ARRAY_DIV, and ARRAY_MOD, two forms are supported for ARRAY types.

In the first form, both left-hand side (LHS) and right-hand side (RHS) operands are two instances of the same ARRAY type.

The arithmetic operation is performed in a pairwise fashion, one by one for each matching pair of elements in the two arguments. If the optional argument scope_reference is defined, then the operation is performed only over the elements within the scope. All elements outside the boundaries of scope_reference are set to NULL. The result is an ARRAY of the same data type as the input ARRAY arguments, with cardinality (number of populated elements) the same as the first input ARRAY argument.

In the second form, one of either LHS or RHS is an ARRAY value with a numeric argument type, and RHS is a numeric value that can be assigned to the element type of LHS. The arithmetic operation is performed by applying the RHS value with the arithmetic operation to each element of the ARRAY argument on the LHS. The result is an ARRAY of the same type as the input ARRAY argument, with cardinality (number of populated elements) the same as the input ARRAY argument. If the optional argument scope_reference is defined, then the operation is performed only over the elements within the scope. All elements outside the boundaries of scope_reference are set to NULL.

For both forms of the arithmetic functions, if a NULL element is encountered in an ARRAY argument, the resulting ARRAY will contain a NULL element in that position. Arithmetic operations involving arrays are not affected by the optional DEFAULT NULL clause in the CREATE TYPE statement. However, the probability of getting an error is increased when comparing arrays that have been created without the DEFAULT NULL clause.

If an ARRAY argument contains any elements that are in an uninitialized state, an error is returned. Use a scope reference to avoid referencing a range of the ARRAY that contains uninitialized elements, or set any uninitialized elements to NULL. You can do this with the OEXTEND method. See OEXTEND.