Counting Parent Child Level

[cookiejar]

We are using the following to retrieve parent child:

 
Cursor get_child_org is
SELECT distinct
       o.ORG_ORGANIZATION_ID org_id,
       o.name belongs_to,
       uic.code org_uic,
       SYS_CONNECT_BY_PATH(o.name, ' - ') "ORG_STRUCTURE"
FROM   ORG_ORGANIZATIONS o,
       org_uic uic
WHERE  o.org_organization_id = uic.org_organization_id(+)
START WITH o.org_organization_id = vParent_id
CONNECT BY PRIOR o.org_organization_id = o.owner_organization_id
Order By ORG_STRUCTURE
;
 

Is there a method to count the parent child level? I would like to return a count to indicate the level versus the parent child path by appending hyphens '-'.

[amitpatel66]

And what is the output that you are getting for your query? Yes CONNECT BY PRIOR is the concept of getting hierarchical data.

[Pilgrim333]

Hi,

As you are adding ' - ' between each node, I found you a simple way of getting the level. Each time you add ' - ' this means you are adding another level. So you need a way to count these additions. You could write a function that counts the number of occurances of a character in a string. On the other hand, a much faster way would be adding the following line to your select clause:

 length(SYS_CONNECT_BY_PATH(o.name, ' -')) - length(SYS_CONNECT_BY_PATH(o.name, '-')) "level"

The first sys_connect_by adds 2 chars to the complete path for each child, the second one adds just one char to the complete path for each child. Subtract the length of these two and you have the number of levels. It counts the parent as one, so if you only have the parent, then the result is one. If you don't want to count the parent, then just substract 1 from to result.

I hope this will help you out, let us know if it does or not. If it doesn't then we'll look for some other solution.

Pilgrim.