Home » SQL & PL/SQL » SQL & PL/SQL » Connect by prior and SUM
| Connect by prior and SUM [message #195180] |
Wed, 27 September 2006 05:42  |
cederin
Messages: 2
Registered: September 2006
|
Junior Member |
|
|
Hello
I have the following table called, Transport, that consits of
a from location, to loaction and a transport time.
Table:Transport
From To Hours
---- --- ----
A B 20
B C 10
C D 10
D E 10
I would like to use the statement, connect by prior, in
order to list all possible transports.
Please Note, that I will use Oracle version
less than 10, so that means that I can not use some connect by features as "connect by root" etc.
The SQL to list all possible transports:
SELECT PARENT.from
CHILD.to
FROM Transport PARENT,Transport CHILD
CONNECT BY PRIOR CHILD.from = CHILD.to AND
PRIOR PARENT.to = PARENT.to
START WITH PARENT.to = CHILD.to
The above will give the following result:
From To
---- ----
A B
A C
A D
A E
B C
B D
B E
.. ..
Thats fine. But, how can I get the total transport time, like below?
Result:
From To Hours
---- -- -----
A B 20
A C 30 (20+10)
A D 40 (20+10+10)
A E 50 (20+10+10+10)
B C 10
B D 20
B E 30
.. .. ..
Could someone give me a hint how to achive this?
Thanks in advance..
Per
|
|
|
|
| Re: Connect by prior and SUM [message #195188 is a reply to message #195180] |
Wed, 27 September 2006 06:15  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
SQL> select from_col
2 , to_col
3 , sum(hours) over (order by from_col)
4 from transport
5 connect by prior to_col = from_col
6 start with from_col = 'A';
F T SUM(HOURS)OVER(ORDERBYFROM_COL)
- - -------------------------------
A B 20
B C 30
C D 40
D E 50
No need for two tables in the from clause.
|
|
|
|
| Re: Connect by prior and SUM [message #195225 is a reply to message #195188] |
Wed, 27 September 2006 08:15 |
cederin
Messages: 2
Registered: September 2006
|
Junior Member |
|
|
Thanks, for your reply.
I think that I was not clear enough in my question.
The above solution will not be OK when adding a new
transport that is not beloning to the same "route".
For ex.
From To Hours
---- -- ----
A H 5
This will give me:
FROM_ TO_CO SUM(HOURS)OVER(ORDERBYFROM_COL)
----- ----- -------------------------------
A B 25
A H 25
B C 35
C D 45
D E 55
I would also like to have the result listed as, all
possible transports with a total transport time, and
not all possible transport legs. That is:
From To Hours
A B 20
A C 30 (20+10)
A D 40 (20+10+10)
A E 50 (20+10+10+10)
A H 5
B C 10
B D 20
B E 30
..
CREATE TABLE transport
(
from_col VARCHAR2(5),
to_col VARCHAR2(5),
hours int
);
insert into transport values('A','B',20);
insert into transport values('B','C',10);
insert into transport values('C','D',10);
insert into transport values('D','E',10);
insert into transport values('A','H',5);
Best Regards / Per
|
|
|
|
| Re: Connect by prior and SUM [message #195959 is a reply to message #195225] |
Tue, 03 October 2006 06:00 |
be2sp1
Messages: 52
Registered: September 2005
Location: India
|
Member |
|
|
cederin,
You could use the following procedure snippet (explained with an example) to suffice your need.
----------------------------------------------------------
create table Transport_temp (From_ varchar2(2), To_ varchar2(2),Hours_ number(3));
----------------------------------------------------------
insert into Transport_temp values ('A','B',10);
insert into Transport_temp values ('B','C',20);
insert into Transport_temp values ('C','D',30);
insert into Transport_temp values ('D','E',40);
insert into Transport_temp values ('A','F',10);
-----------------------------------------------------------
select * from transport_temp;
A F 10
D E 40
C D 30
B C 20
A B 10
----------------------------------------------------------
Create a temporary table for holding the data
create table transport_temp1 as select * from Transport_temp where 1<>1
----------------------------------------------------------
create or replace procedure transport_proc_temp as
FROM_FIRST VARCHAR2(2);
TO_LAST VARCHAR2(2);
a varchar2(2);
b varchar2(2);
c number;
v_level number;
v_sum number :=0;
v_insert number :=0;
type xyz is ref cursor;
temp xyz;
v_query varchar2(2000) := 'select level,from_, to_,hours_ from transport_temp connect by prior To_ = from_';
begin
DBMS_OUTPUT.PUT_LINE ('THIS IS A TRANSPORT_PROC');
open temp for v_query;
loop
fetch temp into v_level,a,b,c;
exit when temp%notfound;
if v_level = 1 then
if v_insert <>0 then
insert into transport_temp1 values (FROM_FIRST,TO_LAST,V_SUM);
v_insert :=1;
end if;
FROM_FIRST := a;
TO_LAST:=b;
v_sum := c;
v_insert :=v_insert+1;
else
TO_LAST:=b;
v_sum := v_sum + c;
v_insert :=v_insert+1;
end if;
end loop;
if v_insert <>0 then
insert into transport_temp1 values (FROM_FIRST,TO_LAST,V_SUM);
end if;
commit;
end transport_proc_temp;
-------------------------------------------------------------
Procedure can be called using -->
begin
transport_proc_temp;
end;
This will insert the following records into the transport_temp1 table.
select * from transport_temp1;
A F 10
A E 100
B E 90
C E 70
D E 40
-------------------------------------------------------------
Use the following query to get the combined result for all the paths.
select * from transport_temp1
union
select * from transport_temp
Resultset-->
A B 10
A E 100
A F 10
B C 20
B E 90
C D 30
C E 70
D E 40
This resultset contains all the probable paths across hierarchies and also the individual paths originally present in the transport table.
-------------------------------------------------------------
|
|
|
|
Goto Forum:
Current Time: Thu May 16 08:30:44 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
