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

On Tue, 18 Mar 2008 10:03:56 -0700 (PDT), Mauro Pagano <mauro.pagano_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 to http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns001.htm#i1007332

This is probably a better place to start: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2053935

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 Received on Tue Mar 18 2008 - 12:29:58 CDT