Getting the very top level parts in a tree using Connect by

Question

I have the following table:

CREATE TABLE Z_BOM2 (A  VARCHAR2(4 CHAR), B  VARCHAR2(4 CHAR));
Insert into Z_BOM2 (A, B) Values ('A', 'B');
Insert into Z_BOM2 (A, B) Values ('A', 'C');
Insert into Z_BOM2 (A, B) Values ('C', 'D');
Insert into Z_BOM2 (A, B) Values ('C', 'F');
Insert into Z_BOM2 (A, B) Values ('D', 'E');
COMMIT;



SELECT * FROM Z_BOM2;
A   B
A   C
C   D
C   F
D   E

A is a Parent and B is a child.

I would like to input child values 'E' and 'F' in a query and get only top level parents for both of those which should be 'A' for both of these. Here is the query I am using:

select SYS_CONNECT_BY_PATH (a,'/') as path, a, b, level, CONNECT_BY_ISLEAF AS leaf, CONNECT_BY_ROOT b top_level
  from Z_BOM2
  connect by prior a = b
  start with b IN ('E', 'F');

Which returns the following:

/D  D   E   1   0   E
/D/C    C   D   2   0   E
/D/C/A  A   C   3   1   E
/C  C   F   1   0   F
/C/A    A   C   2   1   F

Why is it not returning correct top level parent ?

It seems to return the correct top level parent. "/D/C/A A C 3 1 E" for E and "/C/A A C 2 1 F" — Taemyr, Feb 24 '15 at 08:29
Check this image: https://community.oracle.com/servlet/JiveServlet/showImage/2-12914163-114891/a.jpg In my case there can be more than one top level node so I can't start from top. I am only given a value for one of the children and I need to find all the very top level nodes that use that child in their structure. In this picture: the nodes B, A are the top level parents. - if started with child C the top level parents should be B - if started with child D the top level parents should be B, A - if started with child J the top level parent should be A. Hope that makes more sense. — Darko, Feb 24 '15 at 15:53

Alex Poole · Answer 1 · 2015-02-24T18:28:40.520

The top level parent from CONNECT_BY_ROOT is based on your starting condition and the direction you're walking the tree. You're walking the tree backwards, so the 'root' is really your starting condition here.

You actually have the information you want already, but in the A column, for the leaf nodes:

select SYS_CONNECT_BY_PATH (a,'/') as path, a, b, level,
  CONNECT_BY_ISLEAF AS leaf, CONNECT_BY_ROOT b top_level
from Z_BOM2
where CONNECT_BY_ISLEAF = 1
connect by prior a = b
start with b IN ('E', 'F');

PATH                 A    B         LEVEL       LEAF TOP_LEVEL
-------------------- ---- ---- ---------- ---------- ---------
/D/C/A               A    C             3          1 E         
/C/A                 A    C             2          1 F

To only get the top-level value you're interested in, only include column A in the select list.

SQL Fiddle demo.

This works for the structure you added in a comment too; SQL Fiddle. C gets B; J gets A; D gets both A and B:

select CONNECT_BY_ROOT b query_val, a as top_level
from Z_BOM2
where CONNECT_BY_ISLEAF = 1
connect by prior a = b
start with b IN ('C', 'D', 'J');

QUERY_VAL TOP_LEVEL
--------- ---------
C         B         
D         A         
D         B         
J         A

score 0 · Answer 2 · answered Feb 24 '15 at 07:38

Make a WHERE condition on LEVEL

WITH t AS
   (select SYS_CONNECT_BY_PATH (a,'/') as path, a, b, level as LEV,
      CONNECT_BY_ISLEAF AS leaf, CONNECT_BY_ROOT b top_level
   from Z_BOM2
   connect by prior a = b
   start with b IN ('E', 'F'))
SELECT *
FROM t
WHERE LEV = 1;

Getting the very top level parts in a tree using Connect by

2 Answers2