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
>>-ARRAY--[--+---------------------------------------------------+--]-><
+-+-----------------------------------+--fullselect-+
| | .-,-----------------------. | |
| | V | | |
| '-WITH----common-table-expression-+-' |
| .-,----------------------. |
| V | |
'---+-element-expression-+-+------------------------'
'-NULL---------------'
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]