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

On Mar 18, 6:29 pm, NetComrade <netcomradeNS..._at_bookexchange.net> wrote:
> On Tue, 18 Mar 2008 10:03:56 -0700 (PDT), Mauro Pagano
>
>
>
> <mauro.pag..._at_gmail.com> wrote:
> >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 tohttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudo...
>
> This is probably a better place to start:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/querie...
>
> I never actually wrote one, but that's the way to go :)
> Also search asktom.oracle.com for "connect by" for examples
> .......
> We run Oracle 9iR2,10gR2, 10g2RAC on RH4/RH5 and Solaris 10 (Sparc)
> remove NSPAM to email

Netcomrade,
my post is just a "joke" to find different solution to achieve it. The straight way to do what I asked is to use connect_by_isleaf (it's why I've pointed out that doc page).
My question is "in your opinion, which could be another way to achieve it?"
Regards
Mauro Received on Tue Mar 18 2008 - 12:54:04 CDT