I am new to hierarchical queries and I have gone through most of the posts on it.
I have a query that i would like to improve.
My Scenario is something like this:
Sample
-------
ID,
NAME,
PARENT_ID
Table Data as follows:
ID NAME PARENT_ID
-----------------------
1 A (null)
2 B 1
3 C 2
4 D 1
I want the result to be something like this:
ID NAME PARENT_ID IS_LEAF LEVEL DIRECT_CHILDREN_COUNT
-------------------------------------------------------------------
1 A (null) 0 1 2
2 B 1 0 2 1
3 C 2 1 3 0
4 D 1 1 2 0
I wrote the following query which gives all the columns except the DIRECT_CHILDREN_COUNT
SELECT ID, NAME, PARENT_ID, CONNECT_BY_LEAF AS IS_LEAF, LEVEL FROM SAMPLE
START WITH PARENT_ID = null CONNECT BY PRIOR ID = PARENT_ID
There are many posts on how to get the count of all the children of the node(not just the direct children), Those are not applicable to me because
1.I only want the direct children count.
2.If possible I want to achieve this without using joins.
I have no clue how to get the direct children count as a column value for each of the records. Is there anyway I could get the DIRECT_CHILDREN_COUNT using the hierarchical queries?