Change The Level Number In An Sql Query

[nadia74]

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

[tabreaz]

Try out this query. This will help you, may be there is other way around.

For better performance create index on ENAME column.

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