2 Replies Latest reply: Dec 7, 2012 9:55 AM by Frank Kulash RSS

    Start With/Connect by

    Vortex13
      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
          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
            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.