Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calculating in 'connect by prior' queries

UW (Germany)Nov 18 2009 — edited Nov 18 2009
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?
This post has been answered by Frank Kulash on Nov 18 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 16 2009
Added on Nov 18 2009
9 comments
1,370 views