Db2 ordinary arrays can be constructed and populated from an SQL query. Let’s see how to do it.
Creating an array from a row
As you probably know, you can create an array by typing its literal values:
SET xmp_array = [1, 2, 3]
Creating an array from the columns returned by a query is similar:
SET finance_january = [ SELECT income, outcome FROM finance_by_month WHERE year = 2021 AND month = 1 ]
Each column returned by the
SELECT will be an array item. The query must return only one row. Column names or aliases are not important.
Creating an array from a column
But what if we want to run a 1-column
SELECT, and put all the rows into an array? In that case, we can use the
SET sales_2021 = ( SELECT ARRAY_AGG(cost ORDER BY cost) FROM sales WHERE year = 2021 GROUP BY year )
ARRAY_AGG() is an aggregate function that returns an ordinary (non-associative) array. Our example is trivial, but instead of a column you may pass it an expression.
ORDER BY is optional, and only useful if the order of elements is relevant.
DESC are supported.
ARRAY_AGG() is similar to
LIST_AGG(), which returns a list as a string, like a CSV row.