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.Vortex13 wrote:Okay, so that explains that
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.
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?SELECT *
FROM xx_formula
WHERE line_type = -1
START WITH formula_id IN (807) -- Change as needed
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:I get 20 rows of output, not just the 11 you want. I guess I don't understand the requirements.
KT00518
PK15199
PK13947
RM31009
RM30711
RM31004
WP50255
RM30951
RM30948
RM30981
RM30957
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.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.
SELECT -- DISTINCT ?
xx_formula.*
FROM xx_formula
WHERE line_type = -1
START WITH formula_id IN (807) -- Change as needed
AND line_no = 1
AND line_type = 1 -- ***** NEW *****
CONNECT BY ( item = PRIOR item
AND line_type = 1
AND PRIOR line_type = -1
)
OR ( formula_id = PRIOR formula_id
AND line_type = -1
AND PRIOR line_type = 1
)
;
You may still want SELECT DISTINCT . If this is a bill of materials, where a child can have several parents, but you only want to show each item once, then use SELECT DISTINCT.