LucidDbAppLib FLATTEN RECURSIVE HIERARCHY ALL LEVELS

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_ALL_LEVELS(CURSOR(SELECT * FROM t)));

Purpose

Flattens a two-column table specifying a graph and outputs to another table. Unlike the other variation (see link in Notes), this variation creates rows for non-leaf vertices as well.

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.
• The third column contains boolean values indicating whether the output row has a non-leaf starting node.
• Fourth 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

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	Non_leaf	Level1	Level2	Level3	Level4	Level5	Level6	Level7	Level8	Level9	Level10	Level11	Level12	Level13	Level14	Level15
2	false	true	01	02	02	02	02	02	02	02	02	02	02	02	02	02	02
1	false	true	01	01	01	01	01	01	01	01	01	01	01	01	01	01	01
2	false	false	01	03	03	03	03	03	03	03	03	03	03	03	03	03	03
2	false	false	01	04	04	04	04	04	04	04	04	04	04	04	04	04	04
3	false	false	01	02	05	05	05	05	05	05	05	05	05	05	05	05	05
3	false	false	01	02	06	06	06	06	06	06	06	06	06	06	06	06	06
2	false	true	07	08	08	08	08	08	08	08	08	08	08	08	08	08	08
1	false	true	07	07	07	07	07	07	07	07	07	07	07	07	07	07	07
3	false	true	07	08	09	09	09	09	09	09	09	09	09	09	09	09	09
3	false	false	07	08	10	10	10	10	10	10	10	10	10	10	10	10	10
4	false	true	07	08	09	11	11	11	11	11	11	11	11	11	11	11	11
5	false	false	07	08	09	11	12	12	12	12	12	12	12	12	12	12	12
5	false	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