2 Replies Latest reply on Dec 7, 2012 3:55 PM by Frank Kulash

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);``````
--Johnnie
Hi,
Vortex13 wrote:
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.
Okay, so that explains that
``VALUES( 807,'KT00518',-1, 1);``
is the parent of
``VALUES( 1420,'KT00518',1, 1);``
But it looks like there's also another kind of parent-child relationship in this table. That is, the last row I showed above apparantly has children. I'm guessing that
``VALUES( 1420,'RM31009',-1, 1);``
is one of its children. That is, a row with line_type=1 can have children: any row with the same formula_id and line_type=-1 is its child. Is that right?
If so:
``````SELECT     *
FROM     xx_formula
WHERE     line_type     = -1
AND     line_no          = 1
CONNECT BY     (    item          = PRIOR item
AND  line_type          = 1
AND  PRIOR line_type     = -1
)
OR     (    formula_id          = PRIOR formula_id
AND  line_no          = PRIOR line_no + 1
)
;``````
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
I get 20 rows of output, not just the 11 you want. I guess I don't understand the requirements.
Here are my results:
``````FORMULA_ID ITEM        LINE_TYPE    LINE_NO
---------- ---------- ---------- ----------
807 KT00518            -1          1
1420 RM31009            -1          1
1420 RM30711            -1          2
1420 RM31004            -1          3
1420 WP50255            -1          4
3030 RM30951            -1          1
3030 RM30948            -1          2
3030 RM30981            -1          3
3030 RM30957            -1          4
807 PK15199            -1          2
807 PK13947            -1          3
807 KT00518            -1          1
1420 RM31009            -1          1
1420 RM30711            -1          2
1420 RM31004            -1          3
1420 WP50255            -1          4
3030 RM30951            -1          1
3030 RM30948            -1          2
3030 RM30981            -1          3
3030 RM30957            -1          4``````
Take a couple of examples where I'm getting the wrong results, and explain again, using different words, how you get the right results in those places.
Maybe I just need to say SELECT DISTINCT .
In this simple case it would be easy because there are no formulas that are not related to this item,
Yes, it would be better to add a couple of rows that are not related. Why don't you?
but obviously I need a way to do this with a start with/connect by in the real world:
Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful.
Hi,

I see why I was getting duplicate rows in the result set yesterday. Since a row with line_type=1 is the parent of all rows with the same formula_id and line_type=-1, then the START WITH clause should only include rows with line_type=1. So the query I posted yesterday just needs 1 more line:
``````SELECT      -- DISTINCT ?
xx_formula.*
FROM     xx_formula
WHERE     line_type     = -1