Retrieving the first and last array elements (ARRAY_FIRST, ARRAY_LAST functions)

Retrieving the first and last elements in a simple array can most easily be done by using the ARRAY_FIRST and ARRAY_LAST functions.

Before you begin

Read: Ordinary array data type
Read: Restrictions on the array data type
Privileges required to execute the SET statement

About this task

You would perform this task within SQL PL code in order to quickly access the first element in an array.

Procedure

Define a SET statement:

Declare and specify a variable that is of the same type as the array element.
Specify the assignment symbol, '='.
Specify the name of the ARRAY_FIRST or ARRAY_LAST function and within the required brackets, the name of the array.

Results

If the SET statement executes successfully, the local variable should contain the value of the first or last (as appropriate) index value in the array.

Example

For an array of phone numbers defined as:

firstPhone	index    0              	1               2              3
           phone		'416-223-2233'	'416-933-9333'	'416-887-8887'	'416-722-7227'

If the following SQL statement is executed:

SET firstPhoneIx = ARRAY_FIRST(phones);

The variable firstPhoneIx will have the value 0. This would be true even if the element value in this position was NULL.

The following SET statement accesses the element value in the first position in the array:

SET firstPhone = A[ARRAY_FIRST(A)]

What to do next

If the SET statement failed to execute successfully:

Verify the SQL statement syntax of the SET statement and execute the statement again.
Verify that the local variable is of the correct data type.
Verify that the array was created successfully and currently exists.