Array constructor

An array constructor is a language element that can be used to define and construct an array data type value within a valid context.

Syntax

Read syntax diagramSkip visual syntax diagramARRAY[ WITH,common-table-expressionfullselect,element-expressionNULL ]

Authorization

No specific authorizations are required to reference an array constructor within an SQL statement, however for the statement execution to be successful all other authorization requirements for the statement must be satisfied.

Description

WITH common-table-expression
Defines a common table expression for use with the following fullselect.
fullselect
A fullselect that returns a single column. The values that are returned by the fullselect for each row are the elements of the array. The cardinality of the array is equal to the number of rows that are returned by the fullselect. If the fullselect includes an order-by-clause, the order determines the order in which row values are assigned to elements of the array. If no order-by-clause is specified, the order in which row values are assigned to elements of the array is not deterministic.
element-expression
An expression that defines the value of an element in the array. The cardinality of the array is equal to the number of element expressions. The first element-expression is assigned to the array element with array index 1. The second element-expression is assigned to the array element with array index 2 and so on. Every element-expression must have a compatible data type with every other element-expression and the base type of the array is determined using the Rules for result data types topic.
NULL
Specifies the null value.

If no value is specified within the brackets, the result is an empty array.

Rules

  • The base type of the array-constructor, as derived from the element-expressions or the fullselect, must be assignable to the base type of the target array (SQLSTATE 42821).
  • The number of elements in the array-constructor must not exceed the maximum cardinality of the target array variable (SQLSTATE 2202F).

Notes

  • An array constructor can be used to define only an ordinary array with elements that are not a row type. An array constructor cannot be used to define an associative array or an ordinary array with elements that are a row type. Such arrays can only be constructed by assigning the individual elements.

Examples

Example 1: Set the array variable RECENT_CALLS of array type PHONENUMBERS to an array of fixed numbers.
   SET RECENT_CALLS = ARRAY[9055553907, 4165554213, 4085553678]
Example 2: Set the array variable DEPT_PHONES of array type PHONENUMBERS to an array of phone numbers retrieved from the DEPARTMENT_INFO table.
   SET DEPT_PHONES = ARRAY[SELECT DECIMAL(AREA_CODE CONCAT '555' CONCAT EXTENSION,16)
                                      FROM DEPARTMENT_INFO
                                      WHERE DEPTID = 624]