DB2 Version 9.7 for Linux, UNIX, and Windows

ARRAY_PRIOR scalar function


>>-ARRAY_PRIOR--(--array-variable--,--array-index--)-----------><

The schema is SYSIBM.

The ARRAY_PRIOR function returns the next smaller array index value for an array relative to the specified array index argument.

array-variable: 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: Specifies a value that is assignable to the data type of the index of the array. Valid values include any valid value for the data type.

The result is the next smaller array index value 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 defined in the array.

The data type of the result of the function is the data type the array index. The result can be null; if either argument is null, the cardinality of the first argument is zero, or the value of array-index is less than or equal to the value of the first index in the array, the result is the null value.

Examples

Return the previous index value before the 2nd index position in the ordinary array variable SPECIALNUMBERS to the SQL variable PREV_CONSTIDX.
```
SET PREV_CONSTIDX = ARRAY_PRIOR(SPECIALNUMBERS,2)
```
The result is 1.
Given the associative array variable PHONELIST with index values and phone numbers: 'Home' is '4163053745', 'Work' is '4163053746', and 'Mom' is '416-4789683', assign the value of the index in the array that is the previous index before index value 'Work' to the character string variable named X:
```
SET X = ARRAY_PRIOR(PHONELIST, 'Work')
```
The value of 'Mom' is assigned to X. Assign the value of the index in the array that is the previous index before index value'Home':
```
SET X = ARRAY_PRIOR(PHONELIST, 'Home')
```
The null value is assigned to X.