Re: Connect by prior - Flatten out problem

It would help if you had a row for the bosses as well (something like conkey=cifk or cifk is null). Since you don't, I had to do a subquery to get the top bosses first.

select conkey, information_date,

        lag(cifk, hlevel - 1) over (order by rownum) as level1_cifk from (

        select conkey, information_date, cifk, level as hlevel
        from sbcus_test
        connect by prior conkey = cifk
        start with cifk in (
                select cifk
                from sbcus_test employee
                where not exists (
                        select *
                        from sbcus_test boss
                        where boss.conkey = employee.cifk)
                )
        )

      CONKEY INFORMATION_DATE     
LEVEL1_CIFK                                                                                    
------------ --------------------
------------                                                                                    
           2 16-NOV-2001 09:26:21           
1                                                                                    
           4 16-NOV-2001 09:26:21           
3                                                                                    
           5 16-NOV-2001 09:26:21           
3                                                                                    
           6 16-NOV-2001 09:26:21           
3                                                                                    
         301 16-NOV-2001 09:26:21         
300                                                                                    
         302 16-NOV-2001 09:26:21         
300                                                                                    
         303 16-NOV-2001 09:26:21         
300                                                                                    
         501 16-NOV-2001 09:26:21         
500                                                                                    
        1001 16-NOV-2001 09:26:21         1000           

Richard

Toon Schilder wrote:
>
> 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 - 12:05:53 CST