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

On Tue, 18 Mar 2008 09:37:31 -0700 (PDT), Mauro Pagano <mauro.pagano_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 Received on Tue Mar 18 2008 - 11:50:13 CDT