{"id":3297,"date":"2020-02-13T22:11:09","date_gmt":"2020-02-13T21:11:09","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3297"},"modified":"2020-02-13T22:11:09","modified_gmt":"2020-02-13T21:11:09","slug":"hierarchical-query-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3297","title":{"rendered":"Hierarchical query in Oracle"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Below, you find a nice example of an hierarchical query in Oracle. The idea is that you see the top-down hierarchy with some indications on the level that is shown.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First the query:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select  level,\n        case \n        when level=1 then first_name||' '||last_name \n        when level>1 then lpad (first_name||' '||last_name,length(first_name||' '||last_name)+level-1, '-')\n        end tree\nfrom employees  \nstart with employee_id=100\nconnect by prior employee_id=manager_id --top down <\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The result is straight forward to interpret:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2020\/02\/Capture-6-4-1-1.png\" alt=\"\" class=\"wp-image-3298\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">The level is provided, with dashes to provide additional information. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The query can be undertaken from top to bottom as shown here. The idea is that starts with the top, going down to the lower levels. However if the foreign key and primary key are reversed, one starts the analysis from a person to the top, like:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select  level,<br>         case <br>         when level=1 then first_name||' '||last_name <br>         when level>1 then lpad (first_name||' '||last_name,length(first_name||' '||last_name)+level-1, '-')<br>         end tree<br> from employees  <br> start with employee_id=113<br> connect by prior manager_id=employee_id -- down-top<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">, which then leads to another view:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2020\/02\/Capture-6-5-1-1.png\" alt=\"\" class=\"wp-image-3299\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Below, you find a nice example of an hierarchical query in Oracle. The idea is that you see the top-down hierarchy with some indications on the level that is shown. First the query: select level, case when level=1 then first_name||&#8217; &#8216;||last_name when level>1 then lpad (first_name||&#8217; &#8216;||last_name,length(first_name||&#8217; &#8216;||last_name)+level-1, &#8216;-&#8216;) end tree from employees start with [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3297","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3297"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3297\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3297"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}