sys_connect_by_path

sys_connect_by_path is the only function provided by Oracle to get the the hierarchy path in one field, and it is only concatenating.

I just found out a way of doing a sum of the path :

let’s imagine I want the sum of the salary of all my hierarchy.

select ename, sys_connect_by_path(ename,’/’) hierarchy, length(replace(sys_connect_by_path(lpad(‘ ‘,sal/10),’/’),’/’))*10 sal
from emp
connect by mgr=prior empno
start with mgr is null ;

ENAME HIERARCHY SAL
———- —————————— ——-
KING /KING 5000
JONES /KING/JONES 7970
SCOTT /KING/JONES/SCOTT 10970
ADAMS /KING/JONES/SCOTT/ADAMS 12070
FORD /KING/JONES/FORD 10970
SMITH /KING/JONES/FORD/SMITH 11770
BLAKE /KING/BLAKE 7850
ALLEN /KING/BLAKE/ALLEN 9450
WARD /KING/BLAKE/WARD 9100
MARTIN /KING/BLAKE/MARTIN 9100
TURNER /KING/BLAKE/TURNER 9350
JAMES /KING/BLAKE/JAMES 8800
CLARK /KING/CLARK 7450
MILLER /KING/CLARK/MILLER 8750

The LPAD is transforming a number in a string, then length will calculate the length of the calculated string. It is limited to 4000 char. That’s why I divided it by 10.

Let’s do it more flexible with PLSQL

create or replace function eval(expr varchar2) return number is retval number;
begin execute immediate ‘select ‘||expr||’ from dual’ into retval; return retval; end;
/

select ename, sys_connect_by_path(ename,’/’) hierarchy, eval(sys_connect_by_path(sal,’+’)) sal
from emp
connect by mgr=prior empno
start with mgr is null
/

Just using + to add, simple, is not it?