Start of change

ARRAY_AGG

The ARRAY_AGG function returns an array in which each value of the input set is assigned to an element of the array.

Ordinary array aggregation

Read syntax diagramSkip visual syntax diagramARRAY_AGG(‚ÄČexpressionORDER BY,sort-key-expressionASCDESC)

The schema is SYSIBM.

Ordinary array aggregation

expression
Specifies an expression that returns a value with a data type that is valid for an array element. The data type of the expression must be a data type that can be specified in a CREATE TYPE (array) statement.
ORDER BY
Specifies the order of the rows from the same grouping set that are processed in the aggregation. If the ORDER BY clause is not specified, or if the ORDER BY clause cannot differentiate the order of the sort key value, the rows in the same grouping set are arbitrarily ordered.
sort-key-expression
Specifies a sort key value that is either a column name or an expression. sort-key-expression must not reference a column for which a column mask is defined.

If the sort key value is a constant, the constant does not refer to the position of the output column, but is simply a constant, which implies that there is no sort key.

ASC
Processes the sort key in ascending order. This is the default option.
DESC
Processes the sort key in descending order.

The result data type of ARRAY_AGG is an array. The result is an ordinary array. The data type of an array element of the result array is the same as the type of expression.

Start of changeIf a SELECT clause includes multiple invocations of the ARRAY_AGG function, and any invocations of ARRAY_AGG in the SELECT clause explicitly specify an ORDER BY clause, all invocations of ARRAY_AGG must specify an ORDER BY clause, and all ORDER BY clauses must specify the same order.End of change

The ARRAY_AGG function can be invoked only in SQL PL, in the following contexts:

  • The SELECT list of a SELECT INTO statement
  • The SELECT list of the outermost fullselect in the definition of a cursor that is not scrollable
  • The SELECT list of a scalar subquery that provides a source value for a SET assignment-statement or SQL PL assignment-statement
  • A RETURN statement in an SQL scalar function

The following restrictions apply to ARRAY_AGG:

  • ARRAY_AGG cannot be used as part of an OLAP specification.
  • A fullselect that contains an invocation of ARRAY_AGG cannot contain an ORDER BY clause.
  • A fullselect that contains an invocation of ARRAY_AGG cannot contain a DISTINCT keyword in its SELECT list.
  • The SELECT clause or HAVING clause of the fullselect that contains an invocation of ARRAY_AGG cannot contain a subquery.
  • A SELECT clause that includes an invocation of the ARRAY_AGG function that returns an array of LOBs must not also include a GROUP BY clause.
  • A SELECT clause that includes an invocation of the ARRAY_AGG function must not also include an invocation of the XMLAGG function.

Example 1: Use ARRAY_AGG in an assignment statement to assign the values of the DECIMALARRAY ordinary array to the array INTARRAY.

SET INTARRAY = (SELECT ARRAY_AGG(VAL) FROM UNNEST(DECIMALARRAY) AS T(VAL));

Example 2: Use ARRAY_AGG in a SELECT INTO statement to assign the values of the ESALARIES ordinary array to the array ARRAY2.

SELECT ARRAY_AGG(T.VAL) INTO ARRAY2 FROM UNNEST(ESALARIES) AS T(VAL);

Example 3: Use ARRAY_AGG to aggregate a set of phone numbers into an ordinary array. The array elements are then assigned to ordinary array NUMBERS in order by priority, or in arbitrary order.

Suppose that user-defined type PHONELIST and table EMPLOYEE have the following definitions:

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)) ;

The following SQL PL procedure uses a SELECT INTO statement that returns a list of contact numbers under which an employee can be reached, ordered by priority.

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

The following SQL PL procedure uses SET assignment-statement to return the list of 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
End of change