14 Replies Latest reply on Mar 31, 2013 2:24 AM by jampala - oracle

Rollup Hierarchy SQL

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
mce.GDAIS_PROPOSAL_HIERARCHY_V b
WHERE
a.PROPOSAL_NO = 'TKPRICE5' AND
CONNECT BY PRIOR aa.part_no = aa.parent_part_no
• 1. Re: Rollup Hierarchy SQL
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
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
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
Hi,

This gets the results you requested form the sample data Centinul posted:
``````WITH     universe     AS
(
SELECT     part_no
, 2 * (LEVEL - 1)
) || part_no          AS indented_part_no
,     parent_part_no
,     quantity
,     cost
,     ROWNUM                    AS r_num
FROM     t_data
CONNECT BY     parent_part_no     = PRIOR part_no
)
,     bottom_up     AS
(
SELECT     u.*
, LEVEL
, '('
)  || CONNECT_BY_ROOT      TO_CHAR (cost)
|| SYS_CONNECT_BY_PATH ( TO_CHAR (quantity)
, ') * '
)          AS cost_string
FROM     universe     u
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
Thank you for your help. I'm going to start looking at this now.
• 6. Re: Rollup Hierarchy SQL
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
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
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
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
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
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
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
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
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
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
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
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
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.