The ARRAY_EXISTS predicate tests for the existence of an array index in an array.
- An SQL variable, SQL parameter, or global variable of an array type, or a CAST specification of a parameter marker to an array type, or a column in a Hadoop table defined as an array.
- The data type of array-index must be assignable to the data type of the array index of the array. If array-expression is an ordinary array, then array-index must be assignable to INTEGER (SQLSTATE 428H1).
The result is true if array-expression includes an array index that is equal to array-indexcast to the data type of the array index of array-expression; otherwise the result is false.
The result cannot be unknown; if either argument is null, the result is false.
- Assume that array variable
RECENT_CALLSis defined as an ordinary array of array type
PHONENUMBERS. The following IF statement tests if the recent calls list has reached the 40th saved call yet. If it has, the local Boolean variable
EIGHTY_PERCENTis set to true:
IF (ARRAY_EXISTS(RECENT_CALLS, 40)) THEN SET EIGHTY_PERCENT = TRUE; END IF
- This example is based on a Hadoop table with the following
The following statement can be used to query whether the fourth value exists:
CREATE HADOOP TABLE MYTABLE( ID INT, ARR INT ARRAY)
SELECT ID FROM MYTABLE WHERE ARRAY_EXISTS( ARR, 4 )