Hierarchies

I have always referred to traversing a hierarchical data structure as a tree walk. I’m not sure if that is a common term or not. I don’t use them regularly so when I do I generally find myself looking up the syntax of the CONNECT BY PRIOR component. 90% of the time I put the columns┬áin the incorrect order and the query doesn’t work. I learnt a trick to getting this right the first time that I want to share.

My super simple test data

This is the SQL that I generally try to put together. The error in my logic becomes obvious when you see the results

To correct this all I need to do is switch the values on each side of the = sign in the CONNECT BY PRIOR. Which is how I’ve been fixing this for years. If you use this style it needs to be CONNECT BY PRIOR pk_column = fk_column and the data is returned.

The alternative is this nifty trick. You move the PRIOR keyword and it makes more logical sense what you are trying to achieve. Now I’m using CONNECT BY fk_column = PRIOR pk_column.

Other things to keep in mind when you are working with hierarchies
– Hierarchies can have loops of data in them. I forget this because I think about super simple examples like the one above with an employee hierarchy. The CEO is never going to report to someone on the lowest level of your organisation. However in real data it’s more likely to happen

Add a loop of data.

My CEO is now managed by Melanie. ┬áNote that I changed ‘START WITH e.manager_id is null’ to ‘START WITH e.id = 1’

Depending on the version of the database you execute this on the results may differ. I executed on an 11.2 database. In 12 I believe it returns no rows and doesn’t raise the error. Either way it can be handled by adding the NOCYCLE keyword

Other cool Psedocolumns you can use with hierarchies
– CONNECT_BY_ISCYCLE – Returns 1 if the row has a child that is also above it in the hierarchy
– CONNECT_BY_ISLEAF – Returns 1 if the node is a leaf node meaning it has no children.
– SYS_CONNECT_BY_PATH – Outputs the path to this node. Need to supply parameters of column and separator. See example below

 
~~~
Kylie Payne
Dancing With Oracle
@dancewithoracle