GTree Example 2: Show Only Paths That Cycle or End at Leaves - Teradata Vantage

Input

Input tables are from GTree Example 1: Show All Paths from Root Nodes:

• vertices: gtree_vertices
• edges: gtree_edges
• root: gtree_root

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 ('end') 
  Results (
    'Propagate (nodeid) AS start_vertex',
    'Current (nodeid) AS end_vertex',
    'Path (nodestring)',
    'Sum (value)',
    'Cycle()',
    'Leaf()'
  )
  EdgeResults ('PATH(nodestring, endnodestring) AS edgepath')
) AS dt ORDER BY 1,2;

Output