Oracle help using Connect By Root to return assembly top level parent

Question

I'm trying to use CONNECT BY ROOT to do a recursive query that returns the top-level parent of an assembly part. My query so far is only able to return the parent one level above. I created a test case below that replicates the issue. You can also view it in fiddle.

In the real environment my output will have 10K+ rows so if this isn't an efficient way to do it let me know.

-- Create tables
CREATE TABLE t1 (
  TOOL VARCHAR2(50),
  PART VARCHAR2(50),
  PART_ID NUMBER
);

CREATE TABLE item_mstr (
  ITEM_ID NUMBER,
  ITEM VARCHAR2(50)
);

CREATE TABLE item_str (
  PARENT_ID NUMBER,
  CHILD_ID NUMBER
);

INSERT INTO t1(TOOL, PART, PART_ID) VALUES ('tool1', 'assy1', 201);
INSERT INTO t1(TOOL, PART, PART_ID) VALUES ('tool2', 'assy2', 202);
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (101, 'part1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (102, 'part2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (201, 'assy1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (202, 'assy2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (301, 'parent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (302, 'parent2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (401, 'gparent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (402, 'gparent2');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (501, 'top_parent1');
INSERT INTO item_mstr(ITEM_ID, ITEM) VALUES (502, 'top_parent2');
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (201, 101);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (202, 102);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (301, 201);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (302, 202);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (401, 301);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (402, 302);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (501, 401);
INSERT INTO item_str(PARENT_ID, CHILD_ID) VALUES (502, 402);

-- Query
SELECT DISTINCT
t1.TOOL,
t1.PART,
CONNECT_BY_ROOT itm.ITEM TOP_PARENT
FROM item_str str
JOIN t1 on t1.PART_ID = str.CHILD_ID
JOIN item_mstr itm on itm.ITEM_ID = str.PARENT_ID
CONNECT BY PRIOR str.PARENT_ID = str.CHILD_ID
START WITH str.CHILD_ID = t1.PART_ID;

Here's the output I'm looking for:

TOOL   | PART   | TOP_PARENT
-----------------------------
tool1  | assy1  | top_parent1
tool2  | assy2  | top_parent2

Again the issue is the query returns the next parent, in this case 'parent1' and 'parent2', not the top-level.

One thing I noticed is when I add 'WHERE LEVEL > 1' after the joins the output returns nothing. I take that to mean the query sees nothing above the next level so it's not able to see the top-level parent. I'm not sure why though. Thanks for reading!

MT0 · Accepted Answer · 2024-05-29 21:16:41Z

0

You can descend the hierarchy using:

SELECT t.tool,
       t.part,
       m.item
FROM   t1 t
       INNER JOIN (
         SELECT CONNECT_BY_ROOT child_id AS child_id,
                parent_id AS leaf_id
         FROM   item_str
         WHERE  CONNECT_BY_ISLEAF = 1
         CONNECT BY child_id = PRIOR parent_id
       ) i
       ON (t.part_id = i.child_id)
       INNER JOIN item_mstr m
       ON (i.leaf_id = m.item_id);

or ascend the hierarchy using:

SELECT t.tool,
       t.part,
       m.item
FROM   t1 t
       INNER JOIN (
         SELECT child_id,
                CONNECT_BY_ROOT parent_id AS root_id
         FROM   item_str i
         START WITH NOT EXISTS ( SELECT 1
                                 FROM   item_str x
                                 WHERE  i.parent_id = x.child_id )
         CONNECT BY PRIOR child_id = parent_id
       ) i
       ON (t.part_id = i.child_id)
       INNER JOIN item_mstr m
       ON (i.root_id = m.item_id);

Which, for the sample data, both output:

TOOL	PART	ITEM
tool1	assy1	top_parent1
tool2	assy2	top_parent2

fiddle

answered May 29, 2024 at 21:16

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nick Over a year ago

Thanks for the help MT0 but when I implement this into my real environment the query never finishes. I think I oversimplified my test case and now I'm trying to figure out how to communicate things better but not overcomplicate. In my real environment I have multiple old revisions on almost every level of the BOM that this query is fetching. Maybe that's what is slowing it down. I'm troubleshooting down to the sub-queries and for one part in my real environment the innermost sub-query returns seven rows.

Nick Over a year ago

Looking into this more I think a recursive CTE is what I need and I'm finding a lot of help out there on how to use it.

Collectives™ on Stack Overflow

Oracle help using Connect By Root to return assembly top level parent

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related