My World

Small posts here and there about my life

sathya

5 minutes read

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.

So the first query I came up was this:

<span style="font-family: Courier New; font-size: 10pt;"><br /> <span style="color: blue; ">SELECT</span> <span style="color: maroon; ">MENU_SCR_NAME</span><br /> <span style="color: blue; ">FROM</span> <span style="color: maroon; ">MENU_MENUS</span><br /> <span style="color: blue; ">WHERE</span> <span style="color: maroon; ">MENU_PARENT_ID</span> <span style="color: silver; ">=</span> <span style="color: red; ">'101'</span></span>

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

Query results

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.

<span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: maroon;">MENU_SCR_NAME</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">MENU_MENUS</span><br /> <span style="color: blue;">CONNECT</span> <span style="color: blue;">BY</span> <span style="color: blue;">PRIOR</span> <span style="color: maroon;">MENU_ID</span> <span style="color: silver;">=</span> <span style="color: maroon;">MENU_PARENT_ID</span><br /> <span style="color: blue;">START</span> <span style="color: blue;">WITH</span> <span style="color: maroon;">MENU_ID</span> <span style="color: silver;">=</span> <span style="color: red;">'101'</span><span style="color: silver;">;</span><br /> </span>
The result of the query is shown below:

query2

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:
<br /> <span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: #ff0080;"><strong>Lpad</strong></span><span style="color: maroon;">(</span><span style="color: maroon;">menu_scr_name</span><span style="color: silver;">,</span><span style="color: #ff0080;"><strong>Length</strong></span><span style="color: maroon;">(</span><span style="color: maroon;">menu_scr_name</span><span style="color: maroon;">)</span> <span style="color: silver;">+</span> <span style="color: blue;">LEVEL</span> <span style="color: silver;">*</span> <span style="color: black;">4</span> <span style="color: silver;">-</span> <span style="color: black;">4</span><span style="color: silver;">,</span><span style="color: red;">'-'</span><span style="color: maroon;">)</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_menus</span><br /> <span style="color: blue;">WHERE</span> <span style="color: maroon;">menu_id</span> <span style="color: blue;">IN</span> <span style="color: maroon;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">um_menu_id</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_user_menus</span><br /> <span style="color: blue;">WHERE</span> <span style="color: maroon;">um_group_id</span> <span style="color: silver;">=</span> <span style="color: red;">'USGRP'</span><br /> <span style="color: blue;">AND</span> <span style="color: maroon;">um_menu_id</span> <span style="color: blue;">IN</span> <span style="color: maroon;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">menu_id</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_menus</span><br /> <span style="color: blue;">CONNECT</span> <span style="color: blue;">BY</span> <span style="color: blue;">PRIOR</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: maroon;">menu_parent_id</span><br /> <span style="color: blue;">START</span> <span style="color: blue;">WITH</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: red;">'101'</span><span style="color: maroon;">)</span><span style="color: maroon;">)</span><br /> <span style="color: blue;">CONNECT</span> <span style="color: blue;">BY</span> <span style="color: blue;">PRIOR</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: maroon;">menu_parent_id</span><br /> <span style="color: blue;">ORDER</span> <span style="color: blue;">BY</span> <span style="color: maroon;">menu_id</span><br /> </span>
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.

query3

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:
<span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: #ff0080;"><strong>Lpad</strong></span><span style="color: maroon;">(</span><span style="color: maroon;">menu_scr_name</span><span style="color: silver;">,</span><span style="color: #ff0080;"><strong>Length</strong></span><span style="color: maroon;">(</span><span style="color: maroon;">menu_scr_name</span><span style="color: maroon;">)</span> <span style="color: silver;">+</span> <span style="color: blue;">LEVEL</span> <span style="color: silver;">*</span> <span style="color: black;">4</span> <span style="color: silver;">-</span> <span style="color: black;">4</span><span style="color: silver;">,</span><span style="color: red;">'-'</span><span style="color: maroon;">)</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_menus</span><br /> <span style="color: blue;">WHERE</span> <span style="color: maroon;">menu_id</span> <span style="color: blue;">IN</span> <span style="color: maroon;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">um_menu_id</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_user_menus</span><br /> <span style="color: blue;">WHERE</span> <span style="color: maroon;">um_group_id</span> <span style="color: silver;">=</span> <span style="color: red;">'USGRP'</span><br /> <span style="color: blue;">AND</span> <span style="color: maroon;">um_menu_id</span> <span style="color: blue;">IN</span> <span style="color: maroon;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">menu_id</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_menus</span><br /> <span style="color: blue;">CONNECT</span> <span style="color: blue;">BY</span> <span style="color: blue;">PRIOR</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: maroon;">menu_parent_id</span><br /> <span style="color: blue;">START</span> <span style="color: blue;">WITH</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: red;">'101'</span><span style="color: maroon;">)</span><span style="color: maroon;">)</span><br /> <span style="color: blue;">CONNECT</span> <span style="color: blue;">BY</span> <span style="color: blue;">PRIOR</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: maroon;">menu_parent_id</span><br /> <span style="color: blue;">ORDER</span> <span style="color: blue;">SIBLINGS</span> <span style="color: blue;">BY</span> <span style="color: maroon;">menu_id</span><br /> </span>

And it all came into place. Almost.

Have a look:

query4

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 to
<br /> <span style="font-family: Courier New; font-size: 10pt;"><span style="color: blue;">SELECT</span> <span style="color: #ff0080;"><strong>Lpad</strong></span><span style="color: maroon;">(</span><span style="color: maroon;">menu_scr_name</span><span style="color: silver;">,</span><span style="color: #ff0080;"><strong>Length</strong></span><span style="color: maroon;">(</span><span style="color: maroon;">menu_scr_name</span><span style="color: maroon;">)</span> <span style="color: silver;">+</span> <span style="color: blue;">LEVEL</span> <span style="color: silver;">*</span> <span style="color: black;">4</span> <span style="color: silver;">-</span> <span style="color: black;">4</span><span style="color: silver;">,</span><span style="color: red;">'-'</span><span style="color: maroon;">)</span> <span style="color: maroon;">"Menu"</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_menus</span><br /> <span style="color: blue;">WHERE</span> <span style="color: maroon;">menu_id</span> <span style="color: blue;">IN</span> <span style="color: maroon;">(</span><span style="color: blue;">SELECT</span> <span style="color: maroon;">um_menu_id</span><br /> <span style="color: blue;">FROM</span> <span style="color: maroon;">menu_user_menus</span><br /> <span style="color: blue;">WHERE</span> <span style="color: maroon;">um_group_id</span> <span style="color: silver;">=</span> <span style="color: red;">'USGRP'</span><span style="color: maroon;">)</span><br /> <span style="color: blue;">CONNECT</span> <span style="color: blue;">BY</span> <span style="color: blue;">NOCYCLE</span> <span style="color: blue;">PRIOR</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: maroon;">menu_parent_id</span><br /> <span style="color: blue;">START</span> <span style="color: blue;">WITH</span> <span style="color: maroon;">menu_id</span> <span style="color: silver;">=</span> <span style="color: red;">'101'</span><br /> <span style="color: blue;">ORDER</span> <span style="color: blue;">SIBLINGS</span> <span style="color: blue;">BY</span> <span style="color: maroon;">menu_seq_no</span><span style="color: silver;">;</span><br /> </span>

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

query5

Recent posts

See more

Categories

About

test