problem with connect by prior
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: problem with connect by prior

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    problem with connect by prior

    Hi,
    I've table TAB_MGR:

    EMPLOYEE................MANAGER
    ABCD...................ABC
    ABC.....................AB
    AB......................A
    WAXXY..Y...............WAXX
    WAXX...................WA
    WA.....................W

    I tried this query:

    select a.EMPLOYEE, b.MANAGER
    from
    (select EMPLOYEE, MANAGER, rownum-level rl, level lv
    from TAB_MGR connect by prior MANAGER = EMPLOYEE) a,
    (select EMPLOYEE, MANAGER, rownum-level rl, level lv
    from TAB_MGR connect by prior MANAGER = EMPLOYEE) b
    where a.lv=1 and a.rl=b.rl;

    output is:

    EMPLOYEE....................MANAGER
    ABCD........................ABC
    ABCD........................AB
    ABCD........................A
    ABC.........................AB
    ABC.........................A
    AB..........................A
    WAXXYY.....................WAXX
    WAXXYY.....................WA
    WAXXYY.....................W
    WAXX.......................WA
    WAXX.......................W
    WA.........................W

    but I'd like to get also the level 1 of employee = MANAGER

    In my case I'd like to get this output:

    EMPLOYEE........................MANAGER
    ABCD..........................ABCD
    ABCD..........................ABC
    ABCD..........................AB
    ABCD..........................A
    ABC...........................ABC
    ABC...........................AB
    ABC...........................A
    AB............................AB
    AB............................A
    WAXXYY........................WAXXYY
    WAXXYY........................WAXX
    WAXXYY........................WA
    WAXXYY........................W
    WAXX..........................WAXX
    WAXX..........................WA
    WAXX..........................W
    WA............................WA
    WA............................W

    in my query lacks:
    EMPLOYEE........................MANAGER
    ABCD..........................ABCD
    ABC...........................ABC
    AB............................AB
    WAXXYY........................WAXXYY
    WAXX..........................WAXX
    WA............................WA

    How can I get also this record in my query??

    Thanks!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,445

    Cool


    Maybe if you add 'START WITH Employee = Manager' before the CONNECT BY...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

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