Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Montréal, Canada
    Posts
    3

    Unanswered: Special select using CONNECT BY clause

    Hi all

    I want to create an Oracle view with a CONNECT BY clause and I need to have the parent element of the first level in the view.

    Example :

    SELECT employee_id, manager_id, LEVEL
    FROM employees
    CONNECT BY PRIOR employee_id = manager_id;

    The result would be

    Employee_id Manager_id LEVEL
    ------------ ----------- ------
    101 100 1
    102 100 1
    103 100 1
    104 100 1
    110 100 1
    111 110 2
    112 110 2
    113 110 2
    114 110 2
    120 110 2
    121 120 3
    122 120 3
    123 120 3
    124 120 3
    ...

    I want to include the parent of the first level on every rows like that :

    1st LEVEL Parent Employee_id Manager_id LEVEL
    ---------------- ------------ ----------- ------
    100 101 100 1
    100 102 100 1
    100 103 100 1
    100 104 100 1
    100 110 100 1
    100 111 110 2
    100 112 110 2
    100 113 110 2
    100 114 110 2
    100 120 110 2
    100 121 120 3
    100 122 120 3
    100 123 120 3
    100 124 120 3
    ...

    Anyone knows how to do that ?

    Thanks to all

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    On 10G it is easy:
    Code:
    SQL> select empno, mgr, level, connect_by_root empno
      2  from emp
      3  connect by prior empno = mgr
      4  start with mgr is null;
    
         EMPNO        MGR      LEVEL CONNECT_BY_ROOTEMPNO
    ---------- ---------- ---------- --------------------
          7839                     1                 7839
          7566       7839          2                 7839
          7788       7566          3                 7839
          7876       7788          4                 7839
          7902       7566          3                 7839
          7369       7902          4                 7839
          7698       7839          2                 7839
          7499       7698          3                 7839
          7521       7698          3                 7839
          7654       7698          3                 7839
          7844       7698          3                 7839
          7900       7698          3                 7839
          7782       7839          2                 7839
          7934       7782          3                 7839
    
    14 rows selected.
    Prior to 10G I think you would probably need to write a function like this:
    Code:
    SQL> create or replace function connect_by_root(p_empno in number) return number
      2  is
      3    l_empno number;
      4  begin
      5    for r in (select empno from emp
      6              connect by prior mgr = empno
      7              start with empno = p_empno)
      8    loop
      9      l_empno := r.empno;
     10    end loop;
     11    return l_empno;
     12  end;
     13  /
    
    Function created.
    
    SQL> select empno, mgr, level, connect_by_root (empno)
      2  from emp
      3  connect by prior empno = mgr
      4  start with mgr is null;
    
         EMPNO        MGR      LEVEL CONNECT_BY_ROOT(EMPNO)
    ---------- ---------- ---------- ----------------------
          7839                     1                   7839
          7566       7839          2                   7839
          7788       7566          3                   7839
          7876       7788          4                   7839
          7902       7566          3                   7839
          7369       7902          4                   7839
          7698       7839          2                   7839
          7499       7698          3                   7839
          7521       7698          3                   7839
          7654       7698          3                   7839
          7844       7698          3                   7839
          7900       7698          3                   7839
          7782       7839          2                   7839
          7934       7782          3                   7839
    
    14 rows selected.
    ... unless someone knows a slicker way?

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    in v9, you can use SYS_CONNECT_BY_PATH -

    select empno, mgr, level,
    substr( sys_connect_by_path(mgr,'-'),
    2, instr(sys_connect_by_path(mgr,'-')||'-','-',1,2)-2 ) top_level
    connect_by_root empno
    from emp
    connect by prior empno = mgr
    start with mgr is null;


    for record 120 in the original example, the function returns "-100-110-120", so use substr & instr to get the part you want. Since there is no trailing "-", I concatenated one for the instr so it would find something for the top level branch.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Since every root in a hierarchy must have a level of 1, we can also do this using the MAX analytic function as follows..¨
    Code:
    SQL@8i> select empno, mgr, lvl, max( root ) over( order by rownum ) root
      2    from (
      3  select empno, mgr, level lvl, decode( level, 1, empno ) root
      4    from emp
      5   start with mgr is null
      6   connect by prior empno = mgr
      7         )
      8  /
    
         EMPNO        MGR        LVL       ROOT
    ---------- ---------- ---------- ----------
          7839                     1       7839
          7566       7839          2       7839
          7788       7566          3       7839
          7876       7788          4       7839
          7902       7566          3       7839
          7369       7902          4       7839
          7698       7839          2       7839
          7499       7698          3       7839
          7521       7698          3       7839
          7654       7698          3       7839
          7844       7698          3       7839
          7900       7698          3       7839
          7782       7839          2       7839
          7934       7782          3       7839
    
    14 rows selected.
    
    SQL@8i>

  5. #5
    Join Date
    Oct 2004
    Location
    Montréal, Canada
    Posts
    3
    Hi guys

    Big thanks to you ... I finally used the JMartinez's method and it does what I need.

    We are running Oracle 9i.

    Thanks again !

Posting Permissions

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