Oracle Summarized explosion with Prior

Vinum
Vinum used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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) ?
Commented:
No hits so I had some external to write a function to convert the string to a formular.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial