AppLib FLATTEN RECURSIVE HIERARCHY

From Eigenpedia

Contents 1 Syntax 2 Purpose 3 Parameters 4 Examples 5 Source Code

Syntax

-- t is a table of 2 columns (parent and child)
SELECT * FROM TABLE(APPLIB.FLATTEN_RECURSIVE_HIERARCHY(CURSOR(SELECT * FROM t)));

Purpose

Flattens a two-column table specifying a graph and output to another table.

Each row of the input table specifies an edge of a forest (first column contains parent nodes, second column contains child nodes). Each row of the output table represent a flattened path from a root to a leaf.

• The first column contains integer values representing the actual number of vertices on the path.
• The second column contains boolean values true if there exists multiple paths that end at the leaf node of the path.
• Third to last columns contain vertices along the path starting from the root ending at the leaf.

If the number of vertices is less than maxDepth, the path is right-padded with value of the leaf. If the number of vertices is greater than maxDepth, the path is truncated (actual number of vertices on the path before truncating is shown in first column).

Notes:

• the value of maxDepth is currently fixed at 15.
• this function has a variation: LucidDbAppLib FLATTEN RECURSIVE HIERARCHY ALL LEVELS

Parameters

Input table t must:

• have exactly 2 columns as described above;
• be acyclic
• have non-null child node (parent node can be null)

Examples

CREATE TABLE tree1(parent VARCHAR(128), child VARCHAR(128));
INSERT INTO tree1 VALUES
   ('01','02'),
   ('01','03'),
   ('01','04'),
   ('02','05'),
   ('02','06'),
   ('07','08'),
   ('08','09'),
   ('08','10'),
   ('09','11'),
   ('11','12'),
   ('11','13');
SELECT *
FROM TABLE (APPLIB.FLATTEN_RECURSIVE_HIERARCHY(CURSOR(SELECT * FROM tree1)));

The result of the above set of queries is:

Vertices	Multipath	Level1	Level2	Level3	Level4	Level5	Level6	Level7	Level8	Level9	Level10	Level11	Level12	Level13	Level14	Level15
2	false	01	03	03	03	03	03	03	03	03	03	03	03	03	03	03
2	false	01	04	04	04	04	04	04	04	04	04	04	04	04	04	04
3	false	01	02	05	05	05	05	05	05	05	05	05	05	05	05	05
3	false	01	02	06	06	06	06	06	06	06	06	06	06	06	06	06
3	false	07	08	10	10	10	10	10	10	10	10	10	10	10	10	10
5	false	07	08	09	11	12	12	12	12	12	12	12	12	12	12	12
5	false	07	08	09	11	13	13	13	13	13	13	13	13	13	13	13

Source Code

http://p4web.eigenbase.org/open/dev/luciddb/src/com/lucidera/luciddb/applib/cursor/FlattenRecursiveHierarchyUdx.java