They say “Necessity is the Power of Invention” – a quote which I fully understood the impact today. I’ve been trying to understand Oracle’s Hierarchical Queries for a while now, but never fully understood the situation under which or when it would be used. Yesterday I got a reason to use it. Let me begin.

The above picure shows the menu structure of the our application. Now the implementer wanted the menu structure, of each and _every_ module, in a spreadsheet so that he could forward it to the client and get the user authorization thing going(ie, who’s got authority to view/edit etc). Now under each module we have several menus and submenus, and manually entering these would be like ultimate FAIL (later ananlysis showed on an average, each module had about 80 entries). So I was figuring out how to extract this. Now I knew that all these entries were there in the database I was struggling with the query to extract it.

So the first query I came up was this:

SELECTMENU_SCR_NAMEFROMMENU_MENUSWHEREMENU_PARENT_ID='101'

This is what the query results were – a simple output.

Now I knew I’d be going nowhere with this simple query, and I knew normal joins won’t work, so I started looking at Hierarchical queries. Hierarchical queries(going to refer as h-queries) basically allow you to build queries, based on well, hierarchies. Parent – Child Relationships. Tree-Leaf style. Read a bit on h-queries and reconstructed the SQL query to the one below.

While the result of the query looks the same as the first, I knew that it was returning the results in a hierarchy,just check the first image. The keyword for this query is “Connect by” and “Prior” which transforms the query into a h-query. The “connect by” and “prior” gives the conditions for hierarachy in the query, with the column next to “prior” being the child column and the one next to equality being the parent column. The “start with” keyword tells Oracle which is the root record.

Now make things a little complicated, the database contains more records than what’s necessary, and I had to filter them based on user authorization conditions as well. So I added a subquery to filter out those records which belong to the superuser.

So the query now became a little bit more (unnecessarily) complicated:SELECTLpad(menu_scr_name,Length(menu_scr_name)+LEVEL*4-4,'-')FROMmenu_menusWHEREmenu_idIN(SELECTum_menu_idFROMmenu_user_menusWHEREum_group_id='USGRP'ANDum_menu_idIN(SELECTmenu_idFROMmenu_menusCONNECTBYPRIORmenu_id=menu_parent_idSTARTWITHmenu_id='101'))CONNECTBYPRIORmenu_id=menu_parent_idORDERBYmenu_id

The Lpad function is an Oracle PL/SQL function which adds padding of a specified character to the left. The results were not exactly what I was looking for.

A bit of further reading and I came to know that order by destroys the hierarchy, and “order siblings” by is what is supported to be used.

So Modified the query to:SELECTLpad(menu_scr_name,Length(menu_scr_name)+LEVEL*4-4,'-')FROMmenu_menusWHEREmenu_idIN(SELECTum_menu_idFROMmenu_user_menusWHEREum_group_id='USGRP'ANDum_menu_idIN(SELECTmenu_idFROMmenu_menusCONNECTBYPRIORmenu_id=menu_parent_idSTARTWITHmenu_id='101'))CONNECTBYPRIORmenu_id=menu_parent_idORDERSIBLINGSBYmenu_id

And it all came into place. Almost.

Have a look:

Unfortunately, it wasn’t quite right, as the query was going into an almost infinite loop, as the child record itself became the root record.

I had a look at the query again, and realized that my query itself was wrong, as corrected it toSELECTLpad(menu_scr_name,Length(menu_scr_name)+LEVEL*4-4,'-')"Menu"FROMmenu_menusWHEREmenu_idIN(SELECTum_menu_idFROMmenu_user_menusWHEREum_group_id='USGRP')CONNECTBYNOCYCLEPRIORmenu_id=menu_parent_idSTARTWITHmenu_id='101'ORDERSIBLINGSBYmenu_seq_no;

So in essence, I got rid of the unnecessary inner sub-query and voila!