6 Replies Latest reply: Mar 31, 2013 10:55 PM by user10857924 RSS

    Aggregation of only Leaf Nodes in Hierarchical Data (BOM)

    jampala - oracle
      Hi,
      This is similar to request posted in Rollup Hierarchy SQL
      I want to 'Aggregate' only Leaf nodes of the BOM Tree. This is something we need for 'Material Aggregation'. All the Leaf Nodes are Raw Materials and we need to aggregate each of them in a given BOM.

      I am looking for a SQL or PL/SQL which can fetch me the result I am looking for. Any help or guidance is much appreciated.

      Thanks
      JJ

      Test Data:_

      WITH t_data AS
      (
      SELECT 'A' AS part_no, NULL AS parent_part_no, 1 AS quantity FROM DUAL UNION ALL
      SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
      SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity FROM DUAL UNION ALL
      SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
      SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
      SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
      SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL
      )


      Table Structure:_

      Part No Parent Part No Quantity_

      A | | 1
      B | A | 3
      C | B | 4
      F | B | 2
      D | C | 2
      E | C | 1
      D | F | 3

      Expected Result_

      Root Parent Part No Total Quantity_

      A | D | 42
      A | E | 12

      Total Quantity of Leaf Node 'D': 42 (2 X 4 X 3 X 1) + (3 X 2 X 3 X 1)
      Total Quantity of Leaf Node 'E': 12 (1 X 4 X 3 X 2)
        • 1. Re: Aggregation of only Leaf Nodes in Hierarchical Data (BOM)
          Solomon Yakobson
          If you have OLAP option installed:
          WITH t_data AS (
                          SELECT 'A' AS part_no, NULL AS parent_part_no, 1 AS quantity FROM DUAL UNION ALL
                          SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
                          SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity FROM DUAL UNION ALL
                          SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
                          SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
                          SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
                          SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL
                         ),
                    t as (
                          select  part_no,
                                  connect_by_root part_no root_part_no,
                                  dbms_aw.eval_number('1' || sys_connect_by_path(quantity,'*')) branch_quantity
                            from  t_data
                            where connect_by_isleaf = 1
                            start with part_no not in (select nvl(parent_part_no,chr(0)) from t_data)
                            connect by part_no = prior parent_part_no
                         )
          select  part_no,
                  root_part_no,
                  sum(branch_quantity) total_quantity
            from  t
            group by part_no,
                     root_part_no
          /
          
          P R TOTAL_QUANTITY
          - - --------------
          A D             42
          A E             12
          
          SQL>
          Otherwise, you could use xquery:
          WITH t_data AS (
                          SELECT 'A' AS part_no, NULL AS parent_part_no, 1 AS quantity FROM DUAL UNION ALL
                          SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
                          SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity FROM DUAL UNION ALL
                          SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
                          SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
                          SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
                          SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL
                         ),
                    t as (
                          select  part_no,
                                  connect_by_root part_no root_part_no,
                                  '1' || sys_connect_by_path(quantity,'*') branch_quantity
                            from  t_data
                            where connect_by_isleaf = 1
                            start with part_no not in (select nvl(parent_part_no,chr(0)) from t_data)
                            connect by part_no = prior parent_part_no
                         )
          select  part_no,
                  root_part_no,
                  sum(xmlcast(xmlquery(branch_quantity returning content) as number)) total_quantity
            from  t
            group by part_no,
                     root_part_no
          /
          
          P R TOTAL_QUANTITY
          - - --------------
          A D             42
          A E             12
          
          SQL>
          SY.
          • 2. Re: Aggregation of only Leaf Nodes in Hierarchical Data (BOM)
            jampala - oracle
            Hi SY,
            Thank you very much for the prompt and helpful response... Can we do this by using 'Model' clause? I need to run this for all the BOMs in my database. I am under the assumption that 'Model' clause is more efficient in handling manipulation of larger datasets by using appropriate Partitions / Dimensions / Measures.
            Please correct me if my assumption is incorrect.

            Thanks
            JJ
            • 3. Re: Aggregation of only Leaf Nodes in Hierarchical Data (BOM)
              AlexAnd
              SQL> WITH t_data AS
                2   (SELECT 'A' AS part_no, NULL AS parent_part_no, 1 AS quantity
                3      FROM DUAL
                4    UNION ALL
                5    SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity
                6      FROM DUAL
                7    UNION ALL
                8    SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity
                9      FROM DUAL
               10    UNION ALL
               11    SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity
               12      FROM DUAL
               13    UNION ALL
               14    SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity
               15      FROM DUAL
               16    UNION ALL
               17    SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity
               18      FROM DUAL
               19    UNION ALL
               20    SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL)
               21  --
               22  select root, parent, sum(v) as total
               23    from (select connect_by_root part_no as root,
               24                 part_no parent,
               25                 xmlquery (ltrim(sys_connect_by_path(quantity, '*'), '*') returning content).getnumberval() v
               26            from t_data
               27           where connect_by_isleaf = 1
               28           start with part_no =
               29                      (select part_no from t_data where parent_part_no is null)
               30          connect by parent_part_no = prior part_no)
               31   group by root, parent
               32  /
               
              ROOT PARENT      TOTAL
              ---- ------ ----------
              A    D              42
              A    E              12
               
              SQL> 
              • 4. Re: Aggregation of only Leaf Nodes in Hierarchical Data (BOM)
                Solomon Yakobson
                JJ wrote:
                I am under the assumption that 'Model' clause is more efficient in handling manipulation of larger datasets by using appropriate Partitions / Dimensions / Measures.
                Well, I don't think model solution will give you much, if any, advantage. You'd still have to execute hierarchical query first and them apply model while solutions I posted do just hierarchical query with on-the-fly expression calculation. If you are on 11.2 I'd check recursive subquery factoring.

                SY.
                • 5. Re: Aggregation of only Leaf Nodes in Hierarchical Data (BOM)
                  jampala - oracle
                  Hi SY,
                  Yes we are using 11G R2... I guess it is 11.2.0.3... your help is much appreciated.

                  Thanks
                  JJ
                  • 6. Re: Aggregation of only Leaf Nodes in Hierarchical Data (BOM)
                    user10857924
                    SQL> ed
                    Wrote file afiedt.buf
                    
                      1  WITH t_data AS
                      2  (
                      3  SELECT 'A' AS part_no, NULL AS parent_part_no, 1 AS quantity FROM DUAL UNION ALL
                      4  SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
                      5  SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity FROM DUAL UNION ALL
                      6  SELECT 'F' AS part_no, 'B' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
                      7  SELECT 'D' AS part_no, 'F' AS parent_part_no, 3 AS quantity FROM DUAL UNION ALL
                      8  SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity FROM DUAL UNION ALL
                      9  SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity FROM DUAL
                     10  )
                     11  ,
                     12  rec(part_no, parent_part_no, quantity, lvl, str) as
                     13  (
                     14    select part_no, parent_part_no, quantity, 0, cast(part_no as varchar(100))
                     15    from t_data
                     16    where parent_part_no is null
                     17    UNION ALL
                     18    select t.part_no, t.parent_part_no, r.quantity*t.quantity, r.lvl +1, substr(r.str || '/' || t.part_no,1,1)
                     19    from rec r, t_data t
                     20    where r.part_no=t.parent_part_no
                     21  )
                     22  select str, part_no,sum(quantity)
                     23  from rec
                     24  where lvl=(select max(lvl) from rec)
                     25* group by str, part_no
                    SQL> /
                    
                    STR
                    ----------------------------------------------------------------------------------------------------
                    P SUM(QUANTITY)
                    - -------------
                    A
                    D            42
                    
                    A
                    E            12