Vinum
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:
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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) ?