We help IT Professionals succeed at work.

# Oracle Summarized explosion with Prior

on
282 Views
I have to calculate a summarized Bill of material. That is a structure of fx. a machine and I have to calculate the amount of every material summarized in the structure.

Data:
Niveau;Component Part;Qty Per Assembly
0;TEST_NIV0;1
.1;TEST-NIV1;1
..2;TEST-NIV2;      2
...3;TEST-NIV3;3
....4;TEST-NIV4;4
.....5;TEST-NIV5;5
......6;TEST-NIV6;6
.......7;TEST-NIV7;7
........8;TEST-NIV8;8
.........9;TEST-NIV9;9
..........10;TEST-NIV10;10

I have 1 TEST-NIV0. That consist of 1 TEST-NIV1.
TEST-NIV1 consist of 2 TEST-NIV2 - in total, there is 2 (1*1*2) of TEST-NIV2
TEST-NIV2 consist of 3 TEST-NIV3 - in total, there is  6 (1*1*2*3) of TEST-NIV3
TEST-NIV4 consist of 4 TEST-NIV4 - in total, there is  24  (1*1*2*3*4) of TEST-NIV4
and so on...

My program:

``````select
2 as srt,
a.COMPONENT_PART as CP,
case
when level = 1 then a.QTY_PER_ASSEMBLY
else PRIOR a.QTY_PER_ASSEMBLY * a.QTY_PER_ASSEMBLY
end as qty,
prior a.bom_type,
a.bom_type,
a.qty_per_assembly,
prior a.qty_per_assembly
from KK.prod_STRUCTURE a
where a.alternative_no = '*'

CONNECT BY
PRIOR a.COMPONENT_PART = a.PART_NO AND
PRIOR a.CONTRACT = a.CONTRACT AND
PRIOR a.BOM_TYPE = a.BOM_TYPE

and  a.PART_NO in upper('&PART_NO')
order by cp
``````

Result - please see attached file.
The column "IS" is the result of the above, but I want the column "SHOULD BE".
See the formulas in the SUM.XLSX and a description in SUM.JPG

The problem is that I do not get the in-between sums.
Is that possible with or do I have to use a CURSOR?
Sum.xlsx
SUM.jpg
Comment
Watch Question
Commented:
This problem has been solved!
###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant