Re: Count child of every node in hier table reading source just once

On Mar 18, 5:50 pm, NetComrade <netcomradeNS..._at_bookexchange.net> wrote:
> On Tue, 18 Mar 2008 09:37:31 -0700 (PDT), Mauro Pagano
>
>
>
> <mauro.pag..._at_gmail.com> wrote:
> >Hi guys,
> >I'm trying to write a SQL statement to count how many child every node
> >in a hierarchical table has.
> >I want to perform this operation reading source table only one time.
>
> >Following an example of source table:
>
> >CREATE TABLE TEST_HIER
> >(
> >ID NUMBER,
> >PID NUMBER
> >);
>
> >Insert into TEST_HIER
> >(ID, PID)
> >Values
> >(1, 0);
> >Insert into TEST_HIER
> >(ID, PID)
> >Values
> >(2, 1);
> >Insert into TEST_HIER
> >(ID, PID)
> >Values
> >(3, 1);
> >Insert into TEST_HIER
> >(ID, PID)
> >Values
> >(4, 2);
> >Insert into TEST_HIER
> >(ID, PID)
> >Values
> >(5, 3);
> >COMMIT;
>
> >And expected result is:
>
> >PID CHILD
> >---------- ----------
> >0 1
> >1 2
> >2 1
> >3 1
>
> >Any idea?
> >Thanks a lot
> >Mauro
>
> explore connect by clause.
> .......
> We run Oracle 9iR2,10gR2, 10g2RAC on RH4/RH5 and Solaris 10 (Sparc)
> remove NSPAM to email

NetComrade,
are you referring to http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#i1007332 ?
Regards
Mauro Received on Tue Mar 18 2008 - 12:03:56 CDT