Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's an example .. fill in your own column names ...

    select
    LEVEL||' '||lpad(' ',2*(level))||' '||
    R.WORKORDER_TYPE||' '||
    R.WORKORDER_BASE_ID||' '||
    R.WORKORDER_LOT_ID||' '||
    R.WORKORDER_SPLIT_ID| |' '||
    R.WORKORDER_SUB_ID||' '||
    NVL(R.SUBORD_WO_SUB_ID,0)||' '||
    R.OPERATION_SEQ_NO||' '||
    R.PIECE_NO
    from
    REQUIREMENT R
    WHERE
    r.workorder_type = 'W'
    and r.workorder_base_id = '12499'
    and r.workorder_lot_id = '125'
    and r.workorder_split_id = '0'
    and r.SUBORD_WO_SUB_ID IS NOT NULL
    connect by prior
    r.SUBORD_WO_SUB_ID = R.workorder_sub_id
    and r.workorder_split_id = '0'
    and r.workorder_lot_id = '125'
    and r.workorder_base_id = '12499'
    AND r.workorder_type = 'W'
    start with r.WORKORDER_TYPE = 'W'
    and r.WORKORDER_BASE_ID = '12499'
    and r.WORKORDER_LOT_ID = '125'
    and r.WORKORDER_SPLIT_ID = '0'
    AND R.WORKORDER_SUB_ID = '0'
    ;



    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •