Connect by prior - Flatten out

[Verwijderd]

Connect by prior - Flatten out problem

Sorry voor het Engels maar ik had de tekst
ergens anders al in het Engels getypt!

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 datelevel
1216-Nov-01 5:19:17 PM1
3416-Nov-01 5:19:17 PM1
4516-Nov-01 5:19:17 PM2
5616-Nov-01 5:19:17 PM3
30030116-Nov-01 5:19:17 PM1
30130216-Nov-01 5:19:17 PM2
30230316-Nov-01 5:19:17 PM3
50050116-Nov-01 5:19:17 PM1
1000100116-Nov-01 5:19:17 PM1

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