Selecting from an Array Slice

Go Up to Accessing Arrays

The SELECT statement supports syntax for retrieving contiguous ranges of elements from arrays. These ranges are referred to as array slices. Array slices to retrieve are specified in square brackets ([]) following a column name containing an array. The number inside the brackets indicates the elements to retrieve. For a one-dimensional array, this is a single number. For example, the following statement selects the second element in a one-dimensional array:

EXEC SQL
SELECT JOB_TITLE[2]
INTO :title
FROM EMPLOYEE
WHERE LAST_NAME = :lname;

To retrieve a subset of several contiguous elements from a one-dimensional array, specify both the first and last elements of the range to retrieve, separating the values with a colon. The syntax is as follows:

[lower_bound:upper_bound]

For example, the following statement retrieves a subset of three elements from a one-dimensional array:

EXEC SQL
SELECT JOB_TITLE[2:4]
INTO :title
FROM EMPLOYEE
WHERE LAST_NAME = :lname;

For multi-dimensional arrays, the lower and upper values for each dimension must be specified, separated from one another by commas, using the following syntax:

[lower:upper, lower:upper [, lower:upper ...]]

Note:
In this syntax, the bold brackets must be included.

For example, the following statement retrieves two rows of three elements each:

EXEC SQL
DECLARE TC2 CURSOR FOR
SELECT INT_ARR[1:2,1:3]
FROM TABLE1

Because InterBase stores array data in row-major order, the first range of values between the brackets specifies the subset of rows to retrieve. The second range of values specifies which elements in each row to retrieve.

To select data from an array slice, perform the following steps:

Declare a host-language variable large enough to hold the array slice data retrieved. For example,
```
EXEC SQL
BEGIN DECLARE SECTION;
char char_slice[11]; /* 11-byte string for CHAR(10) data type */
long int_slice[2][3];
EXEC SQL
END DECLARE SECTION;
```
The first variable, char_slice, is intended to store a single element from the CHAR_ARR column. The second example, int_slice, is intended to store a six-element slice from the INT_ARR integer column.

Declare a cursor that specifies the array slices to read. For example,

EXEC SQL
DECLARE TC2 CURSOR FOR
SELECT CHAR_ARR[1], INT_ARR[1:2,1:3]
FROM TABLE1

Open the cursor, and the fetch data:

EXEC SQL
OPEN TC2;
EXEC SQL
FETCH TC2 INTO :char_slice, :int_slice;

Advance To:

Updating Data in an Array Slice