Denodo: SPLIT and FLATTEN in the same query statement

Question

regarding to the FLATTEN documentation you have to create a view containing a split first, before you can use FLATTEN. https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/from_clause/flatten_view_flattening_data_structures

In my case I have to query with both steps within the same statement.

Documentation:

viewA = SELECT test1, split(test2, ‘;’) AS test2 FROM source viewB = SELECT * FROM FLATTEN viewA AS V (v.test2)

I need to do something like:

SELECT * FROM FLATTEN (SELECT test1, split(test2, ‘;’) AS test2 FROM source) AS V (v.test2)

Would that be possible?

Answer 1

Try with a common-table expression:

WITH common_table_expression_1
AS (
    SELECT test1, split(test2, ';') AS test2
    FROM source
    )
SELECT *
FROM FLATTEN cte1 AS v(v.test2);