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 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.
First, I started off with a simple hierarchical query:
SELECT LEVEL elevel
,sys_connect_by_path(e.ename, '/') epath
,e.*
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL;
Now I want to apply an Analytic function. I want to know what the next level is.
SELECT LEVEL elevel
,sys_connect_by_path(e.ename, '/') epath
,lead(LEVEL) over(PARTITION BY 1 ORDER BY 1) ellevel
,e.*
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL;
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:
WITH emp_hier AS
(SELECT LEVEL elevel
,sys_connect_by_path(e.ename, '/') epath
,e.*
FROM emp e
CONNECT BY PRIOR e.empno = e.mgr
START WITH e.mgr IS NULL)
SELECT lead(elevel) over(PARTITION BY 1 ORDER BY 1) ellevel
,e2.*
FROM emp_hier e2;
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.
I think even using the CONNECT BY query as an in-line view would result in analytical function being applied after hierarchy is built.
The entire point of this action is to make sure the hierarchical function is executed before the analytical function is applied. I want to make sure all hierarchical functions are applied correctly.
Very interesting, I might have to give this technique a try.