-
Change The Level Number In An Sql Query
Hi
I've this query:
SELECT LEVEL,LPAD(' ',(LEVEL+1)*5,' ')||ENAME ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
the result is:
LEVEL ENAME
1 KING
2 JONES
3 SCOTT
2 FORD
3 SMITH
my question is that if i except FORD how can i change the level number to SMITH from 3 to 2.
for example:
SELECT LEVEL,LPAD(' ',(LEVEL+1)*5,' ')||ENAME ENAME
FROM EMP
WHERE ENAME <> 'FORD'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO= MGR
the result is:
LEVEL ENAME
1 KING
2 JONES
3 SCOTT
2 SMITH
regards
nadia
-
Try out this query. This will help you, may be there is other way around.
For better performance create index on ENAME column.
Code:
SELECT LEVEL, LPAD(' ',(LEVEL+1)*2,' ')||ename ENAME
FROM
(
Select a.ename, a.empno,
DECODE(a.mgr, b.empno, b.mgr, a.mgr) mgr
FROM emp a, (Select empno, mgr FROM emp WHERE ename='JONES') b
WHERE a.ename<>'JONES'
)
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
Last edited by tabreaz; 08-19-2007 at 07:03 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|