Re: tough RECURSIVE QUERY, How?

NNOOR <Nnoor_at_cris.com> wrote in article
<69in86$j7v_at_examiner.concentric.net>...
>
> Hi,
> Following is the scenerio:
>
> An office can have one or more sub-offices. Each of these sub-offices
> can have one or more sub-sub-offices...so on. I have implemented this
> in a single table.
> e.g.
> PARENT_OFF_ID OFFICE_ID
> 20 21
> 20 22
> 21 23
> 24 25
> 23 26
> 23 27 etc etc
>
> For a given office_id, the query should return either just the office_id
> passes as the parameter (if it happens to have NO sub offices) or ALL of
> the LOWEST level offices.
>
> e.g. if passed, 25, it should return 25.
> if passed 20, it should return 22,26,27 (notice NOT 21 and 23)
> if passed 21, it should return 26,27
> if passed 23, it shoudl return 26,27
>
> Can anyone help me with this? Can it be done just thru SQL?
>
> Thanks and Regards,
> Nasir (nnoor_at_cris.com)
>

This can be done with connect by prior and start with options in SQL. I have tried to develop some login in PLSQL for the above. This will work for any level.

PL/SQL Table T1
PL/SQL Table T2
Cursor c1(id number) is select office_id from office where parent_office_id=id;
Begin

T1=Passing Value

Loop

    Loop for all values in T1

         Call cursor c1(values of T1) Loop
           If Cursor is not empty
              Delete the (value of T1) from T2 (if exists)
              Insert the cursor results to T2 if it is not already in T2
              Insert the cursor results to T1 if it is not already in T1
           If Cursor is empty
              Insert the (values of T1) to T2
          End loop
     End loop

     If T1 has the same no. of elements in the previous run and curren run
        Exit
     End if

End loop

dbms_output.put_line Values of T2

End Received on Wed Jan 14 1998 - 00:00:00 CST