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