Connect by prior - Flatten out problem

From: Toon Schilder <tsch_at_xs4all.nl>
Date: 16 Nov 2001 07:44:05 -0800
Message-ID: <9afc2ce8.0111160744.146af5b2@posting.google.com>

Connect by prior - Flatten out problem

I have a problem using the connect by prior statement and getting the right results.

Here's the table:

CREATE TABLE SBCUS_TEST (

  CIFK              NUMBER(10), 
  CONKEY            NUMBER(10), 

  INFORMATION_DATE DATE) Here's the data:
begin

insert into sbcus_test values (1,2, sysdate);
insert into sbcus_test values (3,4, sysdate);
insert into sbcus_test values (4,5, sysdate);
insert into sbcus_test values (5,6, sysdate);
insert into sbcus_test values (500,501, sysdate);
insert into sbcus_test values (300,301, sysdate);
insert into sbcus_test values (301,302, sysdate);
insert into sbcus_test values (302,303, sysdate);
insert into sbcus_test values (1000,1001, sysdate);

end;

The cifk column is the 'boss' or manager of the conkey.

As you can see 3 is boss over 4, 4 is boss over 5 etc. I am only interested in the highest boss. This means that I would like to know the
records:

6,3 (cause the highest boss of record 6 is through 5,6 -> 4,5 --> 3,4)
5,3
4,3 
303, 300

302, 300
301, 300
1001,1000
2,1

Using the following connect by prior statement does the job partially:

select cifk, conkey, information_date, maxlevel from (
select cifk, conkey, information_date, max(level) as maxlevel from sbcus_test
group by cifk, conkey, information_date
connect by prior conkey = cifk)
order by cifk, conkey

I get following information:

cifk    conkey  date			level
1	2	16-Nov-01 5:19:17 PM	1
3	4	16-Nov-01 5:19:17 PM	1
4	5	16-Nov-01 5:19:17 PM	2
5	6	16-Nov-01 5:19:17 PM	3
300	301	16-Nov-01 5:19:17 PM	1
301	302	16-Nov-01 5:19:17 PM	2
302	303	16-Nov-01 5:19:17 PM	3
500	501	16-Nov-01 5:19:17 PM	1
1000	1001	16-Nov-01 5:19:17 PM	1

Now the problem can easily be solved by using a cursor but due to performance reasons I don't want that.

There must be some kind of sql functionality to solve the problem.

Using rank or first_value doesn't work. Maybe the rollup funtion works?

Hope someone can help.

Toon Schilder
Oracle Consultant
Axi Received on Fri Nov 16 2001 - 09:44:05 CST