Usage Notes | ARRAY Comparison Function | Teradata Vantage - Usage Notes - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

For the relational functions ARRAY_GT, ARRAY_GE, ARRAY_LT, ARRAY_LE, ARRAY_EQ, and ARRAY_NE, 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 relational 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 type as the input array arguments, with three possible values: 1 (true), 0 (false), or NULL for all numeric types except DECIMAL(m,n) where m=n. For DECIMAL(m,n) where m=n, the function cannot store a value of 1 because no digits are permitted to the LHS of the decimal point. In this case, the function returns .9[0]. Any higher level of precision above 1 is padded with zero to the right.

In the second form, one of either LHS or RHS is an ARRAY with a valid Teradata data type, and the other operand is a numeric value of the same data type as the one defined for the ARRAY in LHS.

The relational operation is performed by applying the numeric value argument to each element of the 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.

The result is an ARRAY of the same type as the input array argument, with three possible values: 1 (true), 0 (false), or NULL for all numeric types except DECIMAL(m,n) where m=n. For DECIMAL(m,n) where m=n, the function cannot store a value of 1 because no digits are permitted to the LHS of the decimal point. In this case, the function returns .9[0]. Any higher level of precision above 1 is padded with zero to the right. If RHS evaluates to NULL, then NULL is returned.

For both forms of the relational comparison functions, if a NULL element is encountered in an ARRAY argument, the resulting ARRAY will contain a NULL element in that position. Comparison 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.