select rm,
lvl,
ename,
empno,
first_value(no_space_ename) over (partition by vp_num) vp_name,
sal,
(
select sum(
to_number (
substr (sal_str,
instr(sal_str , '+', 1, rownum ) + 1,
instr(sal_str, '+', 1, rownum + 1) -
instr(sal_str , '+', 1, rownum ) - 1
))
)
from dual
connect by level <=
length(
translate (q1.sal_str, chr(9)||'0123456789', ' ')) -1
) sal_sum
from
(
select rm,
lvl,
ename,
empno,
no_space_ename,
sal_str,
max(vp_rm) over (order by rownum) vp_num,
sal
from
(
select rownum rm,
level lvl,
sys_connect_by_path(sal, '+')||'+' sal_str,
decode(level, 2, rownum, null) vp_rm,
lpad(ename, length(ename) + level, ' ') ename,
ename no_space_ename,
empno,
sal
from emp
start with mgr is null
connect by mgr = prior empno
)
) q1
order by rm
Friday, May 18, 2007
Subscribe to:
Posts (Atom)