DB2 Version 9.7 for Linux, UNIX, and Windows

ARRAY_AGG aggregate function


>>-ARRAY_AGG--(--expression--+----------------------------------+-->
                             |           .-,------------------. |   
                             |           V           .-ASC--. | |   
                             '-ORDER BY----sort-key--+------+-+-'   
                                                     '-DESC-'       

>--)-----------------------------------------------------------><

The schema is SYSIBM.

The ARRAY_AGG function aggregates a set of elements into an array. The data type of the expression must be a data type that can be specified in a CREATE TYPE (array) statement (SQLSTATE 429C2).

If sort-key is specified, it determines the order of the aggregated elements in the array. If sort-key is not specified, the ordering of elements within the array is not deterministic. If sort-key is not specified, and ARRAY_AGG is specified more than once in the same SELECT clause, the same ordering of elements within the array is used for each result of ARRAY_AGG.

If a SELECT clause includes an ARRAY_AGG function, then all invocations of ARRAY_AGG, LISTAGG, XMLAGG, and XMLGROUP functions in the same SELECT clause must specify the same order or not specify an order (SQLSTATE 428GZ).

The ARRAY_AGG function can only be specified within an SQL procedure in the following specific contexts (SQLSTATE 42887):

The select-list of a SELECT INTO statement
The select-list of a fullselect in the definition of a cursor that is not scrollable
The select-list of a scalar subquery on the right side of a SET statement

ARRAY_AGG cannot be used as part of an OLAP function (SQLSTATE 42887). The SELECT statement that uses ARRAY_AGG cannot contain an ORDER BY clause or a DISTINCT clause, and the SELECT clause or HAVING clause cannot contain a subquery or call an SQL function (SQLSTATE 42887).

ARRAY_AGG cannot be used to produce an associative array or an array with a row element data type (SQLSTATE 42846).

Example:

Given the following DDL:

 CREATE TYPE PHONELIST AS DECIMAL(10, 0)ARRAY[10]

 CREATE TABLE EMPLOYEE (
   ID          INTEGER NOT NULL,
   PRIORITY    INTEGER NOT NULL,
   PHONENUMBER DECIMAL(10, 0),
   PRIMARY KEY(ID, PRIORITY))

Create a procedure that uses a SELECT INTO statement to return the prioritized list of contact numbers under which an employee can be reached.

 CREATE PROCEDURE GETPHONENUMBERS
   (IN  EMPID   INTEGER,
    OUT NUMBERS PHONELIST)
 BEGIN
   SELECT ARRAY_AGG(PHONENUMBER ORDER BY PRIORITY)
     INTO NUMBERS
   FROM EMPLOYEE
   WHERE ID = EMPID;
 END

Create a procedure that uses a SET statement to return the list of an employee's contact numbers in an arbitrary order.

CREATE PROCEDURE GETPHONENUMBERS
   (IN  EMPID   INTEGER,
    OUT NUMBERS PHONELIST)
 BEGIN
   SET NUMBERS =
     (SELECT ARRAY_AGG(PHONENUMBER)
     FROM EMPLOYEE
     WHERE ID = EMPID);
 END