Change The Level Number In An Sql Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Change The Level Number In An Sql Query

  1. #1
    Join Date
    Aug 2007
    Posts
    4

    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

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    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 08: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
  •  


Click Here to Expand Forum to Full Width