{"id":511,"date":"2011-03-30T15:53:29","date_gmt":"2011-03-30T15:53:29","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=511"},"modified":"2011-03-30T15:53:29","modified_gmt":"2011-03-30T14:53:29","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=511","title":{"rendered":"Analytic vs Hierarchical"},"content":{"rendered":"

While playing around with Analytic functions<\/a> with a Hierarchical function<\/a> in my query I stubled upon some strange behaviour I cannot explain (yet). The documentation provided by Oracle<\/a> states the following:<\/p>\n

\n

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause.<\/p>\n<\/blockquote>\n

Right, that would mean I can apply something analytic and something hierarchical in one query. But when I tried it, it didn’t seem to work the way I expected.<\/p>\n

<\/p>\n

First, I started off with a simple hierarchical query:<\/p>\n

\r\nSELECT LEVEL elevel
\r\n,sys_connect_by_path(e.ename, '\/') epath
\r\n,e.*
\r\nFROM emp e
\r\nCONNECT BY PRIOR e.empno = e.mgr
\r\nSTART WITH e.mgr IS NULL;<\/pre>\n
\"Image1\"<\/p>\n
Now I want to apply an Analytic function. I want to know what the next level is.<\/p>\n
\r\nSELECT LEVEL elevel
\r\n,sys_connect_by_path(e.ename, '\/') epath
\r\n,lead(LEVEL) over(PARTITION BY 1 ORDER BY 1) ellevel
\r\n,e.*
\r\nFROM emp e
\r\nCONNECT BY PRIOR e.empno = e.mgr
\r\nSTART WITH e.mgr IS NULL;\r\n<\/pre>\n
\"Image2\"<\/p>\n
Hey, where did my path value go. It seems that analytic functions screw up the hierarchical function that has been used. I do not understand how this happens, but I figured out a workaround:<\/p>\n
\r\nWITH emp_hier AS
\r\n(SELECT LEVEL elevel
\r\n,sys_connect_by_path(e.ename, '\/') epath
\r\n,e.*
\r\nFROM emp e
\r\nCONNECT BY PRIOR e.empno = e.mgr
\r\nSTART WITH e.mgr IS NULL)
\r\nSELECT lead(elevel) over(PARTITION BY 1 ORDER BY 1) ellevel
\r\n,e2.*
\r\nFROM emp_hier e2;<\/pre>\n
\"Image3\"<\/p>\n
As it turns out, I can force the SQL engine to apply an analytical function last if I make sure the resultset is created using subquery factoring<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
While playing around with Analytic functions with a Hierarchical function in my query I stubled upon some strange behaviour I cannot explain (yet). The documentation provided by Oracle states the following: Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. Right, that would mean I […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,9],"tags":[],"class_list":["post-511","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/511","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=511"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/511\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=511"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=511"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=511"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}