Link to home
Start Free TrialLog in
Avatar of Vinum
VinumFlag for Denmark

asked on

Oracle Summarized explosion with Prior

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 

START WITH a.CONTRACT = upper ('&SITE') 
and  a.PART_NO in upper('&PART_NO')
order by cp

Open in new window


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
Avatar of Vinum
Vinum
Flag of Denmark image

ASKER

I found a hint of using:
SYS_CONNECT_BY_PATH (a.QTY_PER_ASSEMBLY, '*')

That returns a character string like "*1*2*3*4", (for TEST-NIV4).

How do I convert this character string into a computed field (24) ?
ASKER CERTIFIED SOLUTION
Avatar of Vinum
Vinum
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial