Array element specification

The array element specification returns the element from an array specified by array-index.

Read syntax diagramSkip visual syntax diagramarray-expression[ array-index]
array-expression
Start of changeSpecifies an SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type.End of change
[array-index]
An expression that specifies the array index of the element that is to be extracted from the array. An array index value for an ordinary array must be castable to INTEGER. The array index value must be between 1 and the cardinality of the array. An array index value for an associative array must be castable to the data type of the index for the array type. The array index value must represent an element that exists in the array. If the index value is a string that is longer than the index data type, the value is truncated, a warning is issued, and processing continues with the truncated value.

array-index must not be:

  • An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
  • A nondeterministic function
  • A function that is defined with EXTERNAL ACTION
  • A function that is defined with MODIFIES SQL DATA
  • A sequence expression

In EBCDIC code pages, X'BBBB' is normally treated as a concatenation operator. Because this sequence of characters is a pair of right brackets characters (']]') in some EBCDIC code pages, it is not treated as a concatenation operator when used inside of an array-index expression.

The data type of the result is the data type that is specified for the array on the CREATE TYPE (array) statement. If array-index is null, or the array is null, the null value is returned.

If the array element is character or graphic data, the CCSID of the result is the CCSID of the array elements of the array type. If the array element is datetime data, the CCSID of the result is 1208.

Examples

Example 1: Suppose that PHONE_NUMBERS is an array variable that is defined as an array type. The array type is defined as an ordinary array of CHAR(10) elements. Also suppose that INT_VAR is an integer variable. The following assignment statements demonstrate how an index for an array element can be specified.

Set the first element of an array to NULL:

SET PHONE_NUMBERS[1] = NULL;

Set the third element to the value ‘4164789683’:

SET PHONE_NUMBERS[3] = '4164789683';

Set an array element to '4164788888’, and specify the array index with the variable INT_VAR:

SET PHONE_NUMBERS[INT_VAR] = '4164788888';

Set an array element to ‘4164783322’, and specify the array index with the expression INT_VAR+5:

SET PHONE_NUMBERS[INT_VAR + 5] = ‘4164783322’;