This discussion is archived
2 Replies Latest reply: Dec 7, 2012 7:55 AM by Frank Kulash RSS

Start With/Connect by

Vortex13 Explorer
Currently Being Moderated
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 1. Re: Start With/Connect by
    Frank Kulash Guru
    Currently Being Moderated
    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     
    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:
    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.
  • 2. Re: Start With/Connect by
    Frank Kulash Guru
    Currently Being Moderated
    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     
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points