Array element specification
The array element specification returns the element from an array specified by array-index.
- array-expression
Specifies an SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type.
- [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’;