This discussion is archived
14 Replies Latest reply: Mar 30, 2013 7:24 PM by jampala - oracle RSS

Rollup Hierarchy SQL

795486 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you for your help. I'm going to start looking at this now.
  • 6. Re: Rollup Hierarchy SQL
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

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