Array constructor

An array constructor returns an ordinary array. An array constructor is specified by a list of expressions or a fullselect.

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.

fullselect: A fullselect that returns a single column. The data type of the column must be a data type that can be specified in a CREATE TYPE (array) statement as the data type of an array element. The values that are returned by the fullselect are the elements of the array. The cardinality of the array is equal to the number of rows that are returned by the fullselect. An ORDER BY clause in the fullselect can be used to specify the order among the elements of the array. Otherwise, the order is undefined. The data type of the elements of the resulting array is the same as the data type of the result column of the fullselect.
element-expression: An expression that defines the value of an element in the array. The expression must return a value with a data type that can be specified in a CREATE TYPE (array) statement as the data type of an array element. 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. All element expressions must have compatible data types. The data type of the elements of the resulting array are determined based on the rules that are described in Rules for result data types.
NULL: Specifies the null value.

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

Start of change An array constructor cannot be specified in an inline SQL function, and can only be specified in the following specific contexts: End of change

As a source value for a SET assignment-statement statement or an SQL PL assignment-statement statement
As the value that is to be returned in a RETURN statement of a user-defined scalar function

An array constructor cannot be used to construct an associative array. An associative array can be constructed only by assigning values to individual array elements.

Examples

Example 1: Suppose that the array variable RECENT_CALLS has the array type PHONENUMBERS. Assign an array of fixed numbers to RECENT_CALLS.

SET RECENT_CALLS = ARRAY[9055553907, 4165554213, 4085553678];

Example 2: Suppose that the array variable DEPT_PHONES has the array type PHONENUMBERS. Assign array phone numbers that are retrieved from the DEPARTMENT_INFO table to DEPT_PHONES.

SET DEPT_PHONES =
 ARRAY[SELECT DECIMAL(AREA_CODE CONCAT '555' CONCAT EXTENSION,16)
  FROM DEPARTMENT_INFO
  WHERE DEPTID = 624];