Collection Derived Table

Description

A collection-derived-table can be used to convert the elements of an array into values of a column in separate rows. If WITH ORDINALITY is specified, an extra column of data type INTEGER is appended. This column contains the position of the element in the array.

Click here to navigate to the IBM DB2 docs page for this syntax.

Collection Derived Tables are not supported in Snowflake.

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

SELECT
   *
FROM
   UNNEST(array) WITH ORDINALITY

Snowflake

Code 1

SELECT
   *
FROM
   TABLE(
   /*** MSC-ERROR - MSCEWI1021 - UNNEST NOT SUPPORTED ***/
   PUBLIC.UNNEST_UDF('UNNEST(array) WITH ORDINALITY'))

Known issues

The Unnest function is not supported in Snowflake, however, it is possible to replace it with a user-defined function that uses the Flatten functionality, This approach is specific to each Unnest implementation and should be translated individually, that's the reason a stub function is the result of this translation. An example of this approach can be found in the StackOverflow site.

Related EWIs

1. MSCEWI1021: NODE NOT SUPPORTED