14 Replies Latest reply: Mar 30, 2013 9:24 PM by jampala - oracle RSS

    Rollup Hierarchy SQL

    795486
      We are trying to create a report with SQL that rolls up a Bill of Material and are having issues. We are using the connect_by and start_with function but the total cost is not being multiplied by the parent quantity.

      Does anyone have any experience with this?

      Example Data_
      *Part No     Parent Part No      Quantity           Cost                                      Total Unit Cost*
      A              Null                  2              Blank                                       Blank
       B             A                     3              Blank                                       Blank
        C            B                     4              Blank                                       Blank
         D           C                     2              $10                                          $20
         E           C                     1              $5                                           $5
      Expected Results_
      *Part No   Parent Part No        Quantity           Cost                                        Total Unit Cost*
      A              Null                  2          Derived from sum of children ($300)                 $600 ($300 x 2)
       B              A                    3          Derived from sum of children ($100)                 $300 ($100 x 3)
        C             B                    4          Derived from sum of children ($25)                  $100 ($25 x 4)            
         D            C                    2          $10                                                 $20
         E            C                    1           $5                                                 $5
      CASE WHEN total_unit_cost IS NULL THEN
             quantity *  (SELECT SUM(total_cost)
                 FROM (SELECT part_no, 
                              parent_part_no,
                              total_unit_cost * quantity total_cost
                         FROM mce.GDAIS_PROPOSAL_HEADER_V    a,
                              mce.GDAIS_PROPOSAL_HIERARCHY_V b
                WHERE      
                   a.PROPOSAL_NO = 'TKPRICE5' AND
                CONNECT BY PRIOR aa.part_no = aa.parent_part_no
                       START WITH aa.parent_part_no = c.part_no
        • 1. Re: Rollup Hierarchy SQL
          Centinul
          Welcome to the forums!

          The best way to get results if you provide sample data in a form that is easy for the forum members to use. If you can convert your sample data to CREATE / INSERT statements chances are you'll get a response much faster.

          I drafted up the sample data:
          WITH t_data AS
          (
                  SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity, NULL AS cost FROM DUAL UNION ALL
                  SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
                  SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity, NULL AS cost FROM DUAL UNION ALL
                  SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity, 10 AS cost FROM DUAL UNION ALL
                  SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity, 5 AS cost FROM DUAL
          ) 
          • 2. Re: Rollup Hierarchy SQL
            Frank Kulash
            Hi,

            Welcome to the forum!

            In addition to the information that Centinul mentioned, you should also post the version of Oracle you are using. This is especially important with CONNECT BY queries: every version since Oracle 7 has had significant improvements in CONNECT BY capabilities.
            In particular, recursive WITH clauses, introduced in Oracle 11.2, make this job a lot easier. The problem here is that we have to know the total_cost for children before we can compute the total_cost for a parent. Without recursive sub-queries, that either requires a MODEL clause, dynamic SQL (that is, a function that can take a string like '4 * ((1*5) + (2*10))' and return 100), or some XML magic. None are trivial, and people might not be willing to go to the trouble of writing and testing a solution if they have no idea whether it will help upu or not because you don't have the right version of Oracle.
            • 3. Re: Rollup Hierarchy SQL
              795486
              Thanks for help so far. As i am new to the forum, i'm still learning what is needed for a good post.

              We are using Oracle 10gR2 (10.2.0.4).
              • 4. Re: Rollup Hierarchy SQL
                Frank Kulash
                Hi,

                This gets the results you requested form the sample data Centinul posted:
                WITH     universe     AS
                (
                     SELECT     part_no
                     ,     LPAD ( ' '
                                    , 2 * (LEVEL - 1)
                                     ) || part_no          AS indented_part_no
                     ,     parent_part_no
                     ,     quantity
                     ,     cost
                     ,     ROWNUM                    AS r_num
                     FROM     t_data
                     START WITH     part_no          = 'A'
                     CONNECT BY     parent_part_no     = PRIOR part_no
                )
                ,     bottom_up     AS
                (
                     SELECT     u.*
                     ,     LPAD ( '('
                               , LEVEL
                               , '('
                               )  || CONNECT_BY_ROOT      TO_CHAR (cost)
                                    || SYS_CONNECT_BY_PATH ( TO_CHAR (quantity)
                                                    , ') * '
                                                    )          AS cost_string
                     FROM     universe     u
                     START WITH     cost     IS NOT NULL
                     CONNECT BY     part_no     = PRIOR parent_part_no
                )
                SELECT       indented_part_no
                ,       parent_part_no
                ,       quantity
                ,       SUM (eval_number (cost_string)) / quantity     AS cost
                ,       SUM (eval_number (cost_string))          AS total_cost
                FROM       bottom_up
                GROUP BY  indented_part_no
                ,       parent_part_no
                ,       quantity
                ,       r_num
                ORDER BY  r_num
                ;
                Output:
                INDENTED_  PARENT_
                PART_NO    PART_NO   QUANTITY       COST TOTAL_COST
                ---------- ------- ---------- ---------- ----------
                A                           2        300        600
                  B        A                3        100        300
                    C      B                4         25        100
                      D    C                2         10         20
                      E    C                1          5          5
                This is an example of a Yo-Yo Query , where the same section of the graph is traversed multiple times, in alternating directions. In this case, we start with a Top-Down query to get the universe of rows needed in this query, then do a Bottom-Up query to associate each leaf with all of its ancestors.

                When trying to understand complicated queries like this, run each sub-query by itself, and study the reult set of that sub_query.
                You'll see that the cost_string column of bottom_up is a string such as '(((10) * 2) * 4) * 3'. The user-defined function eval_number (see below) evaluates that string as a number, returning the NUMBER 240.

                This query assumes that a row has a non-NULL cost if and only if it has no children. If that's not the case, then the same basic idea will work, but it will be even messier.

                Here's how to create the eval_number function used above:
                CREATE OR REPLACE FUNCTION     eval_number
                (     in_txt     IN     VARCHAR2
                ,     err_val     IN     NUMBER     DEFAULT     NULL
                )
                RETURN     NUMBER
                IS
                --     eval_number attempts to evaluate in_txt as a NUMBER,
                --     returning that value.  If in_txt can not be interpreted,
                --     then err_val is returned.
                
                     result_txt     VARCHAR2 (100);
                     return_val     NUMBER;
                BEGIN
                     EXECUTE IMMEDIATE     'SELECT '
                               ||     in_txt
                               ||     ' FROM dual'
                               INTO     return_val;
                --     dbms_output.put_line (result_val || ' = result_txt in eval_number');
                     RETURN     return_val;
                EXCEPTION
                     WHEN OTHERS
                     THEN
                --          dbms_output.put_line (SQLERRM || ' = error in eval_number');
                          RETURN     err_val;
                END     eval_number
                ;
                /
                SHOW ERRORS
                • 5. Re: Rollup Hierarchy SQL
                  795486
                  Thank you for your help. I'm going to start looking at this now.
                  • 6. Re: Rollup Hierarchy SQL
                    Rob van Wijk
                    And here is a solution without the need for PL/SQL, using the SQL model clause introduced in version 10:
                    SQL> WITH t_data AS
                      2  (
                      3          SELECT 'A' AS part_no, NULL AS parent_part_no, 2 AS quantity, NULL AS cost FROM DUAL UNION ALL
                      4          SELECT 'B' AS part_no, 'A' AS parent_part_no, 3 AS quantity, NULL AS cost FROM DUAL UNION ALL
                      5          SELECT 'C' AS part_no, 'B' AS parent_part_no, 4 AS quantity, NULL AS cost FROM DUAL UNION ALL
                      6          SELECT 'D' AS part_no, 'C' AS parent_part_no, 2 AS quantity, 10 AS cost FROM DUAL UNION ALL
                      7          SELECT 'E' AS part_no, 'C' AS parent_part_no, 1 AS quantity, 5 AS cost FROM DUAL
                      8  )
                      9  , t_tree as
                     10  ( select part_no
                     11         , parent_part_no
                     12         , connect_by_root part_no root_part_no
                     13         , level lvl
                     14         , quantity
                     15         , cost
                     16      from t_data
                     17   connect by parent_part_no = prior part_no
                     18     start with parent_part_no is null
                     19  )
                     20  select part_no        "Part No"
                     21       , parent_part_no "Parent Part No"
                     22       , quantity       "Quantity"
                     23       , cost           "Cost"
                     24       , total_cost     "Total Unit Cost"
                     25    from t_tree
                     26   model
                     27         partition by (root_part_no)
                     28         dimension by (parent_part_no, part_no)
                     29         measures (lvl,quantity,cost,0 total_cost)
                     30         ( total_cost[any,any] order by lvl desc,part_no
                     31           = quantity[cv(),cv()]
                     32             * ( nvl(cost[cv(),cv()],0)
                     33               + nvl(sum(total_cost)[cv(part_no),any],0)
                     34               )
                     35         , cost[any,any] order by lvl desc, part_no
                     36           = total_cost[cv(),cv()] / quantity[cv(),cv()]
                     37         )
                     38   order by root_part_no
                     39       , parent_part_no nulls first
                     40       , part_no
                     41  /
                    
                    Part No    Parent Part No   Quantity       Cost Total Unit Cost
                    ---------- -------------- ---------- ---------- ---------------
                    A                                  2        300             600
                    B          A                       3        100             300
                    C          B                       4         25             100
                    D          C                       2         10              20
                    E          C                       1          5               5
                    
                    5 rows selected.
                    Regards,
                    Rob.
                    • 7. Re: Rollup Hierarchy SQL
                      Aketi Jyuuzou
                      Very great model clause solution "Rob van Wijk" ;-)

                      I consider model clause solution and recursive with clause solution.
                      I did not find effective recursive with clause solution. :8}
                      col parent_part_no for a20
                      
                      WITH t_data(part_no,parent_part_no,quantity,cost) AS(
                      SELECT 'A',NULL,2,NULL FROM DUAL UNION ALL
                      SELECT 'B','A' ,3,NULL FROM DUAL UNION ALL
                      SELECT 'C','B' ,4,NULL FROM DUAL UNION ALL
                      SELECT 'D','C' ,2,  10 FROM DUAL UNION ALL
                      SELECT 'E','C' ,1,   5 FROM DUAL)
                      select part_no,parent_part_no,quantity,cost,
                      cost*quantity as "Total Unit Cost"
                        from t_data
                      start with parent_part_no is null
                      connect by prior part_no = parent_part_no
                      model
                      dimension by(part_no,parent_part_no)
                      measures(quantity,cost,
                               Row_Number() over(order by Level) as LV)
                      rules(cost[any,any] order by LV desc=
                      case when cost[cv(),cv()] is null
                           then sum(cost*quantity)[any,cv(part_no)]
                           else cost[cv(),cv()] end);
                      
                      P  PARENT_PART_NO  QUANTITY  COST  Total Unit Cost
                      -  --------------  --------  ----  ---------------
                      A  null                   2   300              600
                      B  A                      3   100              300
                      C  B                      4    25              100
                      D  C                      2    10               20
                      E  C                      1     5                5
                      • 8. Re: Rollup Hierarchy SQL
                        795486
                        I appreciate the further posts. I'm working on the various options now and will reply when i have it working. Again, thanks for the help!
                        • 9. Re: Rollup Hierarchy SQL
                          795486
                          I have a question about the part_no and parent_part_no. When i run my queries, i'm returning 20k records and i should only have 111. I think its because the part_no is not unique (Part No AA56032-IBLK is listed twice but has two different parents). Should I be using the unique id's of the part_no and the parent_part_no in the connect_by?

                          In my scenario, the part_no and parent_part_no are NOT the unique_ids. They are the actual part_no in the Bill of Material twice but under two different parent_part_no. For example,
                          Part_no        Parent_part_no       Level       Total Unit Cost    Quantity
                          20706123-101     20706123-1          1                          3
                          20706123-107     20706123-101          2                          1
                          AA56032-IBLK     20706123-107          3     0.591                     1
                          M22759/11-22-9     20706123-107          3     0.15645564          8
                          M23053/5-104-9     20706123-107          3     0.28275793          8
                          M23053/5-107-9     20706123-107          3     0.02144708          5
                          MS25036-102     20706123-107          3     0.368634             3
                          SMS001789     20706123-107          3                          3
                          M23053/5-105-9     SMS001789          4     0.0209143             2
                          AA56032-IBLK     20706123-101          2     0.591                     1
                          M23053/5-104-9     20706123-101          2     0.28275793          3
                          • 10. Re: Rollup Hierarchy SQL
                            Frank Kulash
                            Hi,
                            BigWaveDave wrote:
                            I have a question about the part_no and parent_part_no. When i run my queries, i'm returning 20k records and i should only have 111. I think its because the part_no is not unique (Part No AA56032-IBLK is listed twice but has two different parents). Should I be using the unique id's of the part_no and the parent_part_no in the connect_by?

                            In my scenario, the part_no and parent_part_no are NOT the unique_ids. They are the actual part_no in the Bill of Material twice but under two different parent_part_no. For example,
                            Part_no        Parent_part_no       Level       Total Unit Cost    Quantity
                            20706123-101     20706123-1          1                          3
                            20706123-107     20706123-101          2                          1
                            AA56032-IBLK     20706123-107          3     0.591                     1
                            M22759/11-22-9     20706123-107          3     0.15645564          8
                            M23053/5-104-9     20706123-107          3     0.28275793          8
                            M23053/5-107-9     20706123-107          3     0.02144708          5
                            MS25036-102     20706123-107          3     0.368634             3
                            SMS001789     20706123-107          3                          3
                            M23053/5-105-9     SMS001789          4     0.0209143             2
                            AA56032-IBLK     20706123-101          2     0.591                     1
                            M23053/5-104-9     20706123-101          2     0.28275793          3
                            You know what to do now: post some sample data in a form that people can use to re-create the problem and test their solutions.
                            A WITH clause, like Centinul posted, is fine. So are CREATE TABLE and INSERT statements.
                            Also post the results you want to get from that data.
                            Point out where the queries suggested so far are getting the wriong results.
                            • 11. Re: Rollup Hierarchy SQL
                              Rob van Wijk
                              BigWaveDave wrote:
                              I have a question about the part_no and parent_part_no. When i run my queries, i'm returning 20k records and i should only have 111. I think its because the part_no is not unique (Part No AA56032-IBLK is listed twice but has two different parents). Should I be using the unique id's of the part_no and the parent_part_no in the connect_by?
                              Yes, you should.
                              • 12. Re: Rollup Hierarchy SQL
                                795486
                                I wanted to thank everyone for their help. I successfully implemented both methods recommended in the responses.

                                Appreciate the help!
                                • 13. Re: Rollup Hierarchy SQL
                                  jampala - oracle
                                  Hi Rob,
                                  This is an excellent piece of 'Model' clause usage... I have a similar requirement... I want to 'Aggregate' only Leaf nodes of the BOM Tree. This is somethig we need for 'Material Aggregation'. All the Leaf Nodes are Raw Materials and we need to aggregate each of them in a given BOM.

                                  Thanks
                                  JJ

                                  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)

                                  Edited by: JJ on Mar 30, 2013 5:48 PM

                                  Edited by: JJ on Mar 30, 2013 6:26 PM

                                  Edited by: JJ on Mar 30, 2013 6:38 PM

                                  Edited by: JJ on Mar 30, 2013 6:39 PM

                                  Edited by: JJ on Mar 30, 2013 6:39 PM

                                  Edited by: JJ on Mar 30, 2013 6:40 PM
                                  • 14. Re: Rollup Hierarchy SQL
                                    jampala - oracle
                                    Hi Aketi,
                                    I have a similar request to Aggregate only Leaf Nodes of the BOM Tree... I could achieve partially by customizing your solution by traversing bottom-up... Child to Root Parent. But this approcah fails if we have duplicate Leaf Nodes at different Levels of BOM

                                    WITH t_data AS
                                    (
                                    SELECT 'A' AS part_no, NULL AS parent_part_no, 2 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
                                    )

                                    This Works_

                                    select part_no,parent_part_no,quantity,cost,
                                    cost*quantity as "Total Unit Cost"
                                    from t_data2
                                    start with part_no in ('E')
                                    connect by prior parent_part_no = part_no
                                    model UNIQUE SINGLE REFERENCE
                                    dimension by(part_no,parent_part_no)
                                    measures(quantity,cost,
                                    Row_Number() over(order by Level) as LV)
                                    rules(cost[any,any] order by LV asc=
                                    case when cost[cv(),cv()] is null
                                    then sum(cost*quantity)[any,cv(part_no)]
                                    else cost[cv(),cv()] end);

                                    Result

                                    PART NO PARENT PART NO QUANTITY COST Total Unit Cost_

                                    E | C | 1 | 1 | 1
                                    C | B | 4 | 1 | 4
                                    B | A | 3 | 4 | 12

                                    This doesn't work_

                                    select part_no,parent_part_no,quantity,cost,
                                    cost*quantity as "Total Unit Cost"
                                    from t_data2
                                    start with part_no in ('D')
                                    connect by prior parent_part_no = part_no
                                    model UNIQUE SINGLE REFERENCE
                                    dimension by(part_no,parent_part_no)
                                    measures(quantity,cost,
                                    Row_Number() over(order by Level) as LV)
                                    rules(cost[any,any] order by LV asc=
                                    case when cost[cv(),cv()] is null
                                    then sum(cost*quantity)[any,cv(part_no)]
                                    else cost[cv(),cv()] end);

                                    Error Message:_

                                    ORA-32638: Non unique addressing in MODEL dimensions
                                    32638. 00000 - "Non unique addressing in MODEL dimensions"
                                    *Cause:    The address space defined for the MODEL (partition by and dimension by
                                    expressions) do not uniquely identify each cell.
                                    *Action:   Rewrite the MODEL clause. Using UNIQUE SINGLE REFERENCE
                                    option might help.