tag:blogger.com,1999:blog-34810287527432012532024-03-13T07:04:23.454-07:00Raj PL/SQLraj pl/sqlhttp://www.blogger.com/profile/12314933288006167146noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-3481028752743201253.post-88508861611777641262007-05-18T11:24:00.000-07:002007-05-18T11:55:56.027-07:00Query :select rm,<br /> lvl,<br /> ename,<br /> empno,<br /> first_value(no_space_ename) over (partition by vp_num) vp_name,<br /> sal,<br /> (<br /> select sum( <br /> to_number (<br /> substr (sal_str, <br /> instr(sal_str , '+', 1, rownum ) + 1, <br /> instr(sal_str, '+', 1, rownum + 1) - <br /> instr(sal_str , '+', 1, rownum ) - 1<br /> ))<br /> )<br /> from dual<br /> connect by level <= <br /> length(<br /> translate (q1.sal_str, chr(9)||'0123456789', ' ')) -1 <br /> ) sal_sum<br />from<br />(<br />select rm,<br /> lvl, <br /> ename, <br /> empno,<br /> no_space_ename,<br /> sal_str,<br /> max(vp_rm) over (order by rownum) vp_num,<br /> sal<br />from<br />(<br />select rownum rm,<br /> level lvl,<br /> sys_connect_by_path(sal, '+')||'+' sal_str,<br /> decode(level, 2, rownum, null) vp_rm,<br /> lpad(ename, length(ename) + level, ' ') ename,<br /> ename no_space_ename,<br /> empno,<br /> sal<br />from emp<br />start with mgr is null<br />connect by mgr = prior empno<br />)<br />) q1<br />order by rmraj pl/sqlhttp://www.blogger.com/profile/12314933288006167146noreply@blogger.com