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 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
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.
-------------------------------------------------------------
Previous Topic: Regarding VARRAY ...
Next Topic: Can you Tune this Query?
Goto Forum:
  


Current Time: Sun Dec 04 18:23:52 CST 2016

Total time taken to generate the page: 0.17989 seconds