Avatar of Vinum
Vinum
Flag 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
Oracle Database

Avatar of undefined
Last Comment
Vinum

8/22/2022 - Mon
Vinum

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
Vinum

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck