ARRAY_PRIOR scalar function
The ARRAY_PRIOR function returns the next smaller 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_PRIOR is the next smaller array index value that is defined in the array, relative to the specified array-index value. If array-index is greater than the maximum index array value in the array, the result is the last 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 less than or equal to the value of the first 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 PREV_CONSTIDX variable to the value of the array index for the SPECIALNUMBERS array element that precedes the array element that is associated with an array index value of 2.
SET PREV_CONSTIDX = ARRAY_PRIOR(SPECIALNUMBERS,2);
The result is 1.
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 precedes an array index value named 'Fax' to the character string variable named X.
SET X = ARRAY_PRIOR(PHONELIST,'Fax');Array index value 'Fax' does not exist, but the string 'Cell' precedes the string 'Fax' in sorting order. Therefore, 'Cell' is assigned to X.
Assign the array index value that precedes array index value 'Cell' to the character string variable named X.
SET X = ARRAY_PRIOR(PHONELIST,'Cell');The null value is assigned to X, because there is no array element before the array element with the index value 'Cell'.