CODEWITHSUNDEEP
sqloracle-databasehierarchical-query
Darko
Darko

Reputation: 203

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

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 ?

Upvotes: 2

Views: 1532

Answers (2)

Alex Poole
Alex Poole

Reputation: 191285

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

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

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;

Upvotes: 0

Related Questions