Retrieving the next and previous array elements

Retrieving the next or previous elements in a simple array can most easily be done by using the ARRAY_PRIOR and ARRAY_NEXT functions.

Before you begin

About this task

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

Procedure

  1. Define a SET statement:
    1. Declare and specify a variable that is of the same type as the array element.
    2. Specify the assignment symbol, '='.
    3. Specify the name of the ARRAY_NEXT or ARRAY_PRIOR function and within the required brackets, the name of the array.
  2. Execute the SET statement.

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'
The following SQL statement sets the variable firstPhone to the value 0..
SET firstPhone = ARRAY_FIRST(phones);
The following SQL statement sets the variable nextPhone to the value 1.
SET nextPhone = ARRAY_NEXT(phones, firstPhone);
The following SQL statement sets the variable phoneNumber to the value of the phone number at the next position in the array after nextPhone. This is the array element value at index value position 2.
SET phoneNumber = phones[ARRAY_NEXT(phones, nextPhone)];

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.