.-,-----------------------.
V |
>>-+-UNNEST-+--+-(----ordinary-array-variable-+--)-+-----------><
'-TABLE--' +-(--associative-array-variable--)--+
'-(--array-function-invocation--)---'
The schema is SYSIBM.
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.
- 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
- 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
- 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