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

# Aggregation of only Leaf Nodes in Hierarchical Data (BOM)

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)
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)
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)
``````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
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)
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)
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)
``````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``````