ARRAY_NEXT scalar function
The ARRAY_NEXT function returns the next larger array index value for an array, relative to a specified array index argument.
The schema is SYSIBM.
- array-expression
An SQL variable,
SQL parameter, or global variable of an array type, or a CAST specification that specifies an SQL
variable, SQL parameter, global variable, or parameter marker as the source value.
- array-index
- An expression that results in a value that is castable to the
data type of the array index. Valid values include any valid value
for the data type.array-index must not be an expression that references any of the following items:
- 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
The result of ARRAY_NEXT is the next larger array index value defined in the array, relative to the specified array-index value. If array-index is less than the minimum index array value in the array, the result is the first array index value that is defined in the array.
The data type of the result has the same data type as the array index.
- array-expression or array-index is null
- The array that is represented by array-expression is empty (the cardinality of the array is 0)
- The value of array-index is greater than or equal to the value of the last index in the array
Notes
- Syntax alternatives:
-
CAST (SQL-variable AS array-type) can be specified as an alternative to SQL-variable. CAST (SQL-parameter AS array-type) can be specified as an alternative to SQL-parameter.
Example 1: Suppose that SPECIALNUMBERS is an ordinary array variable, and the elements of the array are integers. The cardinality of SPECIALNUMBERS is 10. Set the NEXT_CONSTIDX variable to the value of the array index for the SPECIALNUMBERS array element that follows the array element that is associated with an array index value of 9.
SET NEXT_CONSTIDX = ARRAY_NEXT(SPECIALNUMBERS,9);
The result is 10.
Example 2: Suppose that PHONELIST is an associative array variable with VARCHAR index values. Values have been assigned to the elements in the array with the following statements:
SET PHONELIST['Home'] = '4443051234';
SET PHONELIST['Work'] = '4443052345';
SET PHONELIST['Cell'] = '4447893456';The order in which values are assigned to array elements in an associative array does not matter. The elements of an associative array are stored in the array variable in ascending order of the associated array index values. After the values have been assigned to the PHONELIST array variable using the SET assignment-statement statements, the elements in the array variable are ordered as follows:
| Index value | Element value |
|---|---|
| Cell | 4447893456 |
| Home | 4443051234 |
| Work | 4443052345 |
Assign the array index value that follows an array index value named 'Fax' to the character string variable named X.
SET X = ARRAY_NEXT(PHONELIST,'Fax');Array index value 'Fax' does not exist, but the string 'Home' follows the string 'Fax' in sorting order. Therefore, 'Home' is assigned to X.
Assign the value of the array element with index X to the SQL variable NUMBER_TO_CALL.
SET NUMBER_TO_CALL = PHONELIST[X];Because the value of X is 'Home', the assignment statement assigns the phone number '4443051234' to NUMBER_TO_CALL.