The
UNNEST function returns a result table that includes a row for each
element of the specified array. If there are multiple ordinary array
arguments specified, the number of rows will match the array with
the largest cardinality.
.-,-----------------------.
V |
>>-+-UNNEST-+--+-(----ordinary-array-variable-+--)-+-----------><
'-TABLE--' +-(--associative-array-variable--)--+
'-(--array-function-invocation--)---'
The schema is SYSIBM.
- ordinary-array-variable
- An SQL variable, SQL parameter, or global
variable of an ordinary array type, or a CAST specification
of a parameter marker to an ordinary array type.
- associative-array-variable
- An SQL variable, SQL parameter, or global
variable of an associative array type, or a CAST specification
of a parameter marker to an associative array type.
- array-function-invocation
- A function invocation which resolves to a function that returns
an ordinary or an associative array type.
Names
for the result columns produced by the UNNEST function can be provided
as part of the correlation-clause of the collection-derived-table clause.
The
UNNEST function can only be used in a collection-derived-table clause
in a context where arrays are supported (SQLSTATE 42887).
The result table depends on the input arguments.
- If a single ordinary array argument or an
array-function-invocation returning an ordinary array is specified:
- If the array element is not a row data type, the result is a single
column table with a column data type that matches the array element
data type.
- If the array element is a row data type, the result is a table
with one column for each row field in the element data type. The result
table column data types match the corresponding array element row
field data types.
- If more than one ordinary array argument is specified and none
of the array elements have a row data type, the first array provides
the first column in the result table, the second array provides the
second column, and so on. The data type of each column matches the
data type of the array elements of the corresponding array argument.
If the cardinalities of the arrays are not identical, the cardinality
of the resulting table is the same as the array with the largest cardinality.
The column values in the table are set to the null value for all rows
whose array index value is greater than the cardinality of the corresponding
array. In other words, if each array is viewed as a table with two
columns, one for the array indexes and one for the data, then UNNEST
performs an OUTER JOIN among the arrays, using equality on the array
indexes as a join predicate.
- If a single associative array argument or
an array-function-invocation returning an associate array is specified:
- If the array element is not a row data type, the result is a table
with 2 columns where the first column data type matches the array
index data type and the second column data type matches the array
element data type.
- If the array element is a row data type, the result is a table
with one more column than the number of fields in the row data type,
where the first column data type matches the array index data type
and the remaining column data types match the array element row field
data types.
- An error is returned (SQLSTATE 42884):
- If more than one associative array argument is specified.
- If more than one array argument is specified and at least one
of the arrays has a element data type that is a row type.
- If both ordinary array arguments and associative array arguments
are specified.
This
special table function is only used in collection-derived-table of table-reference in
a FROM clause.
If more than one array is provided and at least
one of the arguments is an associative array, an error is returned
(SQLSTATE 42884).
If the WITH ORDINALITY clause is used when
unnesting an associative array, an error is returned (SQLSTATE 428HT).
Examples
- Example 1: Assume the ordinary array
variable RECENT_CALLS of array type PHONENUMBERS contains
only the three element values 9055553907, 4165554213, and 4085553678.The
following query:
SELECT T.ID, T.NUM
FROM UNNEST(RECENT_CALLS) WITH ORDINALITY AS T(NUM, ID)
returns
a table formatted as follows:ID NUM
----------
1 9055553907
2 4165554213
3 4085553678
- Example 2: Return the list of personal
phone numbers from the array variable PHONELIST of
array type PERSONAL_PHONENUMBERS along with the index
string values The following query:
SELECT T.ID, T.PHONE
FROM UNNEST(PHONELIST) AS T(ID, PHONE)
returns
a table formatted as follows:ID PHONE
----------------
Home 4163053745
Work 4163053746
Mom 4164789683
DB2 Version 10.1 for Linux, UNIX, and Windows