DB2 Version 9.7 for Linux, UNIX, and Windows

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 diagram
>>-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

Notes

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]