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 ARRAY_AGG() function:
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. ASC and DESC are supported.
ARRAY_AGG() is similar to LIST_AGG(), which returns a list as a string, like a CSV row.
Reference
Db2 documentation: