ARRAY_EXISTS predicate

The ARRAY_EXISTS predicate tests for the existence of an array element with the specified index in an array.

Read syntax diagramSkip visual syntax diagramARRAY_EXISTS( array-expression, array-index)
array-expression
Specifies one of the following items:
  • An SQL variable, SQL parameter, or global variable of an array type
  • A CAST specification of an array or parameter marker to an array type.
array-index
Specifies the index for the array element that is to be tested. An array index value for an ordinary array must be castable to INTEGER. An array index value for an associative array must be castable to the data type of the array index.
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 the ARRAY_EXISTS predicate is:

  • True if array-expression includes an array index that is equal to the result of casting array-index to the data type of the array index of array-expression.
  • False under either of the following conditions:
    • array-expression does not include an array index that is equal to the result of casting array-index to the data type of the array index of array-expression.
    • Either argument is null.
  • Cannot be unknown.
Example: Suppose that array variable RECENT_CALLS is defined as an ordinary array of array type PHONENUMBERS. The following IF statement tests whether the recent calls list has reached the 40th saved call. If it has, the local integer variable EIGHTY_PERCENT is set to 1: 
IF (ARRAY_EXISTS(RECENT_CALLS, 40))
 THEN SET EIGHTY_PERCENT = 1;
END IF