Re: tough RECURSIVE QUERY, How?

Shajith, T V wrote:
>
> 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)
> >
>
> Nasir,
>
> I know this can be achieved using connect by prior and start with commands.
> Here I tried to develop some logic without any of these facility. I have
> checked this logic for 5 levels and its working fine.
>
> 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
>
> Print the values from T2
>
> End
>
> Thanks,

You can try with 'Connect by and start with ' Clause in select statement.

Vijay Received on Thu Jan 15 1998 - 00:00:00 CET