GTree Example: All Paths from Root Nodes | Teradata Vantage - GTree Example: All Paths from Root Nodes - Teradata Vantage

This example shows all paths from root nodes.

Input

The vertices (nodes) are bus stops in a small town. The Vertices table lists each bus stop and the boarding fare at that stop.

The Edges table represents the bus route. The columns nodeid and nodestring identify the source vertices (where the bus starts) and the columns endnodeid and endnodestring identify the target vertices (where the bus stops).

The Root table defines the set of root vertices from which the function starts traversing the graph.

SQL Call

SELECT * FROM GTree (
  ON gtree_vertices AS Vertices PARTITION BY nodeid, nodestring
  ON gtree_edges AS Edges PARTITION BY nodeid, nodestring
  ON gtree_root AS Root PARTITION BY nodeid, nodestring
  USING
  TargetKey ('endnodeid', 'endnodestring')
  AllowCycles ('t')
  MaxDepth (10)
  OutputType ('all')
  Results (
    'Propagate (nodeid) AS start_vertex',
    'Current (nodeid) AS end_vertex',
    'Path (nodestring)',
    'Sum (value1)',
    'Cycle()',
    'Leaf()'
  )
  EdgeResults ('PATH(nodestring, endnodestring) AS edgepath')
) AS dt ORDER BY 1,2;

Output

The output table has one column for each function that the Results or EdgeResults syntax element specifies. The edgepath column shows the links that comprise the path, the cycle column shows whether the path is a cycle, and the sum column shows the total fare for the path (the sum of the boarding fares at each node in the path).

 start_vertex end_vertex path_nodestring_                               sum_value1_ cycle__ leaf__ edgepath         
 ------------ ---------- ----------------                               ----------- ------- ------ ---------------- 
            1          1 park st->                                          2.25    false   false  .-park stwalnut st->.
            1          3 park st->walnut st->                                4.5    false   false  .-park stwalnut st->.-walnut stwater st->.
            1          4 park st->walnut st->water st->                      8.0    true    false  .-park stwalnut st->.-walnut stwater st->.-water stpark st->
            1          4 park st->walnut st->water st->                      8.0    false   false  .-park stwalnut st->.-walnut stwater st->.-water sthigh st->.
            1          5 park st->walnut st->water st->high st             10.25    false   true   .-park stwalnut st->.-walnut stwater st->.-water sthigh st->.
            2          1 main st->walnut st->water st->park st->           10.25    true    false  .-main stwalnut st->.-walnut stwater st->.-water stpark st->.-park stwalnut st->
            2          2 main st->                                          2.25    false   false  .-main stwalnut st->.
            2          3 main st->walnut st->                                4.5    false   false  .-main stwalnut st->.-walnut stwater st->.
            2          4 main st->walnut st->water st->                      8.0    false   false  .-main stwalnut st->.-walnut stwater st->.-water stpark st->.
            2          4 main st->walnut st->water st->                      8.0    false   false  .-main stwalnut st->.-walnut stwater st->.-water sthigh st->.
            2          5 main st->walnut st->water st->high st             10.25    false   true   .-main stwalnut st->.-walnut stwater st->.-water sthigh st->.

Download a zip file of all examples and a SQL script file that creates their input tables.