ARRAY_FIRST scalar function

The ARRAY_FIRST function returns the minimum array index value of an array.

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.

The result of ARRAY_FIRST has the same data type as the array index. If array-expression is not null, and the array is not empty (the cardinality of the array is greater than 0), the value of the result is the minimum array index value, which is 1 for an ordinary array.

The result can be null; if the argument is null, the result is the null value.

If the array is empty (the cardinality of the array is 0), the result is the null value.

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. Return the first index value in the array variable SPECIALNUMBERS to the SQL variable E_CONSTIDX.

SET E_CONSTIDX = ARRAY_FIRST(SPECIALNUMBERS);

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 value of the first index in the array variable to the character string variable named X.

SET X = ARRAY_FIRST(PHONELIST);

The value of 'Cell' is assigned to X because 'Cell' is the index value of the first element in the array variable.

Assign the value of the array element with index X to the SQL variable NUMBER_TO_CALL.

SET NUMBER_TO_CALL = PHONELIST[X];

The assignment statement assigns the phone number '4447893456' to NUMBER_TO_CALL.