15.00 - ARRAY Element Reference - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

ARRAY Element Reference

Purpose  

Accesses the value of a specified element in an ARRAY data type value.

Syntax  

where:

 

Syntax element…

Specifies…

array_expression

an expression that evaluates to an ARRAY data type.

index_value

the index to the element in the array whose value you want to access.

For a 1-D ARRAY type, index_value must be a positive integer in the range from 1 to n, where n is the declared size of the ARRAY type.

For an n-D ARRAY type, index_value must be a positive or negative integer in the range from m to n, where m is the declared lower bound of a dimension of the array, and n is the declared upper bound.

ANSI Compliance

ARRAY element reference for a one-dimensional ARRAY data type is ANSI SQL:2011 compliant.

ARRAY element reference for an multidimensional ARRAY data type is a Teradata extension to the ANSI SQL:2011 standard.

Usage Notes  

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 SQL Data Definition Language. For information about the ARRAY constructor expression, see “ARRAY Constructor Expression” on page 195.

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.

Using ARRAY Element Reference with a SET Clause

You can use an ARRAY element reference as the target value in a SET clause of an UPDATE or MERGE statement to set the value of an individual element in an ARRAY.

If you set an element in the middle of a 1-D array to a specific value, and the elements previous to this one are not yet initialized, then these elements will be automatically initialized to null. For an n-D ARRAY type, if you set an element in the middle of the array to a specific value, and the elements previous to this one, across all dimensions in row-major order, are not yet initialized, then these elements will be automatically initialized to null.

Note that if the element type of the array is a variable-length type, and if the value passed to initialize the element is larger than the maximum size of the element type, Teradata Database automatically truncates the passed value to the maximum size of the element type. This truncation behavior occurs with transaction processing in Teradata session mode only. In ANSI session mode, Teradata Database does not truncate the passed value, and returns an error instead. This behavior is identical to that of distinct and structured UDTs.

In addition, existing implicit CAST functionality, such as casting from CHAR to Timestamp or Timestamp to DATE, is not supported for the ARRAY element reference. The source data type for the SET clause must be the same as the element data type of the array. When the target of the SET clause is an ARRAY element reference, you must use an explicit CAST for the source value of a SET clause that has a different data type than the element type of the array.

Example  

If a table contains a column named ephone, and the data type of the ephone column is a 1-D ARRAY, then the following shows the ARRAY element reference syntax to access the value of the 5th element of the array:

   ephone[5]

Example

Consider the following 1-D ARRAY data type:

   CREATE TYPE phonenumbers_ary AS CHAR(10) ARRAY[5];

The following statement creates a table with a column named ephone with a data type of phonenumbers_ary.

   CREATE TABLE my_table (eno INTEGER, ephone phonenumbers_ary);

The following statement uses an ARRAY element reference to set element 3 of the array to the value '5551234567'. If elements 1 and 2 in the array were not initialized, this UPDATE statement automatically sets those elements to null.

   UPDATE my_table
   SET ephone[3] = '5551234567';

Example  

Consider the following structured UDT:

   CREATE TYPE measures_UDT AS(amplitude INTEGER,
                               phase     INTEGER,
                               frequency INTEGER);

The following statement creates a 3-D ARRAY data type named shots with an element type of measures_UDT.

   CREATE TYPE shots AS measures_UDT ARRAY[-2:2][-5:5][-3:3];

The following statement creates a table with a column named shot_ary. The data type of the shot_ary column is shots, which is a 3-D ARRAY type.

   CREATE TABLE seismic_table (id INTEGER, shot_ary shots);

The following query selects the value of an element from the 3-D array.

   SELECT shot_ary[-1][1][3] FROM seismic_table;