AppLib FLATTEN RECURSIVE HIERARCHY ALL LEVELS

From LucidDB Wiki
Jump to: navigation, search

Contents

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:

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

Retrieved from "http://luciddb.org/wiki/index.php?title=AppLib_FLATTEN_RECURSIVE_HIERARCHY_ALL_LEVELS&oldid=6778"
Product Documentation