ARRAY_FIRST scalar function
The ARRAY_FIRST function returns the minimum array index value of the array.
The schema is SYSIBM.
- array-expression
- 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 the result is the data type of the array index, which is INTEGER for an ordinary array. If array-expression is not null and the cardinality of the array is greater than zero, the value of the result is the minimum array index value, which is 1 for an ordinary array.
The result can be null; if array-variable is null or the cardinality of the array is zero, the result is the null value.
Examples
- Return the first index value in the ordinary array variable
SPECIALNUMBERS
to the SQL variableE_CONSTIDX
.
The result is 1.SET E_CONSTIDX = ARRAY_FIRST(SPECIALNUMBERS)
- 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 minimum index in the array to the character string variable namedX
.
The value of 'Home' is assigned toSET X = ARRAY_FIRST(PHONELIST)
X
. Access the element value associated with index value 'Home' and assign it to the SQL variableNUMBER_TO_CALL
:SET NUMBER_TO_CALL = PHONELIST[X]
- Return
the first index value in the ARRAY column from a Hadoop
table.
CREATE HADOOP TABLE t1 (c1 INT, c2 DOUBLE ARRAY[5]); SELECT c1, ARRAY_FIRST(c2) FROM t1;