Calculating in 'connect by prior' queries
Let me describe my problem with the following example.
create table subparts
(part varchar2(10),
subpart varchar2(10),
amount number);
insert into subparts values ('A1','B1',5);
insert into subparts values ('A1','B2',5);
insert into subparts values ('A2','B1',4);
insert into subparts values ('A2','B2',2);
insert into subparts values ('A1','C1',8);
insert into subparts values ('A2','C2',16);
insert into subparts values ('B1','C1',8);
insert into subparts values ('B1','C2',4);
insert into subparts values ('B2','C1',4);
insert into subparts values ('B2','C2',6);
Lets assume for example that
A's are different tpyes of cabinets,
B's are different types of drawers and
C's are different types of screws.
In a cabinet there are certain numbers of different drawers and I need a certain amount of the different screw types for each drawer and additional screws for the cabinet itself.
Now I want to query, how many screws of a certain type do I need in total for the different cabinet types.
I can start my query with something like
select toplevel,bottomlevel, structure, '1'||multiply_amount amount from
(
select connect_by_root part toplevel,
subpart bottomlevel,
sys_connect_by_path(subpart,' > ') structure,
sys_connect_by_path(to_char(amount),'*') multiply_amount
from subparts
connect by prior subpart = part
) where toplevel like 'A%' and bottomlevel like 'C%';
TOPLEVEL BOTTOMLEVE STRUCTURE AMOUNT
-------- ---------- ---------- ------
A1 C1 > B1 > C1 1*5*8
A1 C2 > B1 > C2 1*5*4
A1 C1 > B2 > C1 1*5*4
A1 C2 > B2 > C2 1*5*6
A1 C1 > C1 1*8
A2 C1 > B1 > C1 1*4*8
A2 C2 > B1 > C2 1*4*4
A2 C1 > B2 > C1 1*2*4
A2 C2 > B2 > C2 1*2*6
A2 C2 > C2 1*16
If it would be possible to calculate the multiplications in the amount column to a number, I would get the desired result with a SUM(AMOUNT) GROUP BY TOPLEVEL, BOTTOMLEVEL on the above result set.
Is there something like sys_mulitply_by_path similar to sys_connect_by_path? Or is there an other easy solution, that I don't see?