CONNECT FIELD BY RECURSIVE TABLE

CREATE MULTISET VOLATILE TABLE VTBL_BASE,
NO FALLBACK ,
CHECKSUM = DEFAULT,
NO LOG
AS(
SELECT
id,
name,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY name) AS row_no
FROM
DWHDEV.TBL_TEST_RECURSIVE
) WITH DATA
PRIMARY INDEX
(id)
ON COMMIT PRESERVE ROWS
;

WITH RECURSIVE temp_table (id, name, row_no, depth) AS
(
SELECT
id,
name,
row_no,
depth
FROM
(SELECT DISTINCT
id,
CAST('' AS VARCHAR(1000)) AS name,
0 AS row_no,
0 AS depth
FROM
VTBL_BASE
)tbl

UNION ALL

SELECT
temp_table.id,
temp_table.name || ' ' || base_table.name,
temp_table.row_no + 1 AS row_no,
temp_table.depth + 1 AS newdepth
FROM
temp_table
INNER JOIN
VTBL_BASE AS base_table
ON
temp_table.id = base_table.id
AND
temp_table.row_no + 1 = base_table.row_no
WHERE
newdepth < 10
)

SELECT
id,
name
FROM
temp_table
WHERE
(id, row_no)
IN
(SELECT
id,
MAX(row_no)
FROM
temp_table
GROUP BY
id
)
ORDER BY
id
;

DROP TABLE VTBL_BASE
;

Tweet