Re: [Q] Hierarchical Data Query

can your tree have more than one roots ? does connect by prior work properly in these circumstances ?

you could write a recursive function to do the job.

function rootCN(vCN)
vpar varchar2;
begin

    select parent_CN into vpar from tablexx where vCN=CN;     if vpar is null then

        return vCN;
    else return rootCN(vpar);
    end if;
end;

regards,
nitin.
--

Bradley G. Smith <bgsmith/r6pnw_deschutes_at_fs.fed.xxus> wrote in message news:otfzvguecajqrfpuhgrfsfsrqhf.ffadfd0.pminews_at_news.newsguy.com...
> I have a table which contains data with parent child relationships. Each
> record has:
>
> CN Parent_CN
>
> The "root" of each tree contained the table is the record with a
"Parent_CN
> is null" condition. I would like to create a view that has:
>
> CN Parent_CN Root_CN
>
> where Root_CN is the CN number of the root to which CN belongs. Each CN
> belongs to 1 and 1 only tree. This view allows a reference to CN to
quickly
> find the root of the tree it belongs to.
>
> The START WITH & CONNECT BY PRIOR clauses of Oracle allow a parent to find
> all children, but does it also allow a child to find it's root parent? I
> believe the traversal is only in one direction.
>
> What is the solution to this problem? I would appreciate all help.
>
> Thanks,
>
> Brad
>
>
>
Received on Fri Jul 23 1999 - 13:29:48 CDT