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

Oracle Database

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

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 questionI 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

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) ?