The ARRAY data type is a composite data value that consists of zero or more elements of a specified data type (known as the element type). VARARRAY data type allows the size of an individual element value to exceed its declared size as long as the total size of the array is smaller than the array's SQL width.

The VARARRAY type is most compatible with the ABL array data definitions. For best compatibility with the ABL, use the VARARRAY type. The ARRAY type is less ABL compatible and more SQL standard compliant.

Syntax

This is the syntax for the array data type:

data_type ARRAY[int] | VARARRAY[int]

Parameters

data_type

The data type of the array. This is also known as the element type.

Supported data types are: BINARY, BIT, CHAR, VARCHAR, DATE, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT, TIME, TIMESTAMP, TIMESTAMP_TZ, TINYINT, and VARBINARY.

[int]

An unsigned integer, indicating the array's maximum element size.

Notes

• OpenEdge SQL limits an array's size. The array's size must be an integer between 1 and 9999.
• Array columns and element references cannot be indexed because:
  • You cannot define a UNIQUE key with columns of type ARRAY.
  • You cannot define a PRIMARY key with columns of type ARRAY.
  • You cannot define a FOREIGN key with columns of type ARRAY.
• Array columns and element references cannot be used in GROUP BY clauses.

Example

In this example, table TBL is created. TBL has two columns: column C1 is an array of up to 3 elements, all of them type int and column C2 is a variable-sized array of up to 4 elements, all of them type varchar:

CREATE TABLE TBL (C1 int ARRAY[3], C2 varchar(5) VARARRAY[4]);

The size of any element in C2 can be up to 20 characters (5*4) with a total size of 20 characters.