Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Start With/Connect by

Vortex13Dec 6 2012 — edited Dec 7 2012
DB is 11gR1

I have a table that defines formulas for items that are created (line_type = 1) and what ingredients are needed to make it (line_type = -1). Some ingredients also need to be made, so they will have another record in the table (different formula) with a line_type of 1, and the ingredients that make it up. Those ingredients could also be created, and so on.

So in the example below formula 807 creates item 4112949142. The line_type = -1 for formula 807 define the ingredients that make up that item. One of the ingredients, KT00518, is also a manufactured item, defined by that item with line_type = 1 which is formula 1420. Ingredient WP50255 is manufactured with formula 3030.

What I need to be able to do, is starting with formula 807, recursively loop through all the formulas to get all of the ingredients needed to make that item:
KT00518
PK15199
PK13947
RM31009
RM30711
RM31004
WP50255
RM30951
RM30948
RM30981
RM30957

In this simple case it would be easy because there are no formulas that are not related to this item, but obviously I need a way to do this with a start with/connect by in the real world:
SELECT *
  FROM XX_FORMULA
 WHERE line_type = -1
DROP TABLE XX_FORMULA;
CREATE TABLE XX_FORMULA
(
  FORMULA_ID     NUMBER          NOT NULL,
  ITEM           VARCHAR2(60)    NOT NULL,
  LINE_TYPE      NUMBER          NOT NULL,
  LINE_NO        NUMBER          NOT NULL
); 
--Top Level
INSERT INTO XX_FORMULA
VALUES( 807,'4112949142',1, 1);
INSERT INTO XX_FORMULA
VALUES( 807,'KT00518',-1, 1);
INSERT INTO XX_FORMULA
VALUES( 807,'PK15199',-1, 2);
INSERT INTO XX_FORMULA
VALUES( 807,'PK13947',-1, 3);
--Middle
INSERT INTO XX_FORMULA
VALUES( 1420,'KT00518',1, 1);
INSERT INTO XX_FORMULA
VALUES( 1420,'RM31009',-1, 1);
INSERT INTO XX_FORMULA
VALUES( 1420,'RM30711',-1, 2);
INSERT INTO XX_FORMULA
VALUES( 1420,'RM31004',-1, 3);
INSERT INTO XX_FORMULA
VALUES( 1420,'WP50255',-1, 4);
--Leaf
INSERT INTO XX_FORMULA
VALUES( 3030,'WP50255',1, 1);
INSERT INTO XX_FORMULA
VALUES( 3030,'RM30951',-1, 1);
INSERT INTO XX_FORMULA
VALUES( 3030,'RM30948',-1, 2);
INSERT INTO XX_FORMULA
VALUES( 3030,'RM30981',-1, 3);
INSERT INTO XX_FORMULA
VALUES( 3030,'RM30957',-1, 4);
Thanks in advance for your help.
--Johnnie
This post has been answered by Frank Kulash on Dec 7 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 4 2013
Added on Dec 6 2012
2 comments
147 views