1

Topic: db2 9.7 ARRAY_AGG

Kind time of days! There are such data structures

CREATE OR REPLACE TYPE TYPE_ROW AS ROW (
"ID" INTEGER;
"DATE1" TIMESTAMP;
"DATE2" TIMESTAMP;
"NAME" VARCHAR (20))
@
CREATE OR REPLACE TYPE TYPE_ROW_ARR AS TYPE_ROW ARRAY []
@

Whether it is possible somehow, using SELECT to fill array TYPE_ROW_ARR? I.e. for example

....
DECLARE VAR_ARRAY TYPE_ROW_ARR
....
SELECT ARRAY_AGG (T.ID), ARRAY_AGG (T.DATE1), ARRAY_AGG (T.DATE2), ARRAY_AGG (T.NAME)
--Or can ARRAY_AGG (T.ID, T.DATE1, T.DATE2, T.NAME)
INTO VAR_ARRAY
FROM TABLE1 AS T

Or any other variants without a cycle on "SELECT". Here SSEPGG_9.7.0 write

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

That the type data structure "ROW" (my case) will not work. In advance thanks!

2

Re: db2 9.7 ARRAY_AGG

Tried with "BULK COLLECT"

....
DECLARE VAR_ARRAY TYPE_ROW_ARR
....
SELECT T.ID, T.DATE1, T.DATE2, T.NAME
BULK COLLECT INTO VAR_ARRAY
FROM TABLE1 AS T

There is an error

SQL0104N The wrong element "COLLECT" after the text Is found out