The element of the array being referenced must be initialized to a value or to null. If the referenced array element is in an uninitialized state, an error is returned. You can use the DEFAULT NULL clause to initialize all elements of an array to null at the time the ARRAY data type is created, or you can use the ARRAY constructor expression to initialize the array. For information about the DEFAULT NULL clause, see “CREATE TYPE (Array Form)” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. For information about the ARRAY constructor expression, see ARRAY_Constructor_Expression.
For a 1-D ARRAY type, you can reference a single element using the ARRAY element reference syntax with one set of square brackets. For example, MyArray[n], where MyArray is a 1-D ARRAY data type.
For an n-D ARRAY type, you can reference a single element using the ARRAY element reference syntax with n sets of square brackets, where n corresponds to the number of dimensions of the n-D ARRAY type. For example, if my3DArray defines a 3-D ARRAY data type, then the ARRAY element reference syntax for accessing one element of this array would be my3DArray[x][y][z]. Note that the maximum number of sets of square brackets is five, which corresponds to the maximum number of dimensions that Teradata supports for n-D ARRAY types.
The number of dimensions referenced in an ARRAY element reference is validated when you attempt to access the element in the array value using the element reference. This requires that an ARRAY value to be referenced already exists, and that the SQL statement actually executes the element reference. For this reason, there are some rare cases where an SQL statement that includes an ARRAY element reference may not actually be executed. For example, a SELECT from a table where the table contains no rows, or a WHERE clause in a SELECT which contains an element reference on the right-hand side of an OR clause, where the left-hand side evaluates to true. In these types of cases, the behavior of an ARRAY element reference is similar to a UDF invocation.