This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Feb 20, 2013 1:43 AM by 980503 Go to original post RSS
  • 15. Re: Connect by
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Oracle_Monkey wrote:
    ... Can you explain one example with explanation.
    Sure; let's take the example Blushadow posted earlier, {message:id=10857349}. Let's add SYS_CONNECT_BY_PATH; that often makes CONNECT BY output easier to understand.
    So the query is:
    SELECT  empno
    ,     LEVEL                         AS lvl
    ,     PRIOR empno                     AS prior_empno
    ,     mgr
    ,     RPAD ( ' '
              , (LEVEL - 1) * 2
              , ' '
              ) || ename                    AS ename
    ,       SYS_CONNECT_BY_PATH (ename, '/')     AS lineage
    FROM    scott.emp
    CONNECT BY     mgr     = PRIOR empno
    START WITH     mgr      IS NULL
    ;
    Output:
    `         PRIOR_
    EMPNO LVL  EMPNO   MGR ENAME        LINEAGE
    ----- --- ------ ----- ------------ -------------------------
     7839   1              KING         /KING
     7566   2   7839  7839   JONES      /KING/JONES
     7788   3   7566  7566     SCOTT    /KING/JONES/SCOTT
     7876   4   7788  7788       ADAMS  /KING/JONES/SCOTT/ADAMS
     7902   3   7566  7566     FORD     /KING/JONES/FORD
     7369   4   7902  7902       SMITH  /KING/JONES/FORD/SMITH
     7698   2   7839  7839   BLAKE      /KING/BLAKE
     7499   3   7698  7698     ALLEN    /KING/BLAKE/ALLEN
     7521   3   7698  7698     WARD     /KING/BLAKE/WARD
     7654   3   7698  7698     MARTIN   /KING/BLAKE/MARTIN
     7844   3   7698  7698     TURNER   /KING/BLAKE/TURNER
     7900   3   7698  7698     JAMES    /KING/BLAKE/JAMES
     7782   2   7839  7839   CLARK      /KING/CLARK
     7934   3   7782  7782     MILLER   /KING/CLARK/MILLER
    Every row in the result set got there either by
    (1) meeting the START WITH condition (in which case, the row is one LEVEL=1, and there is no PRIOR row), or
    (2) meeting the CONNECT BY condition (in which case the row is on LEVEL=N, and the PRIOR row is on LEVEL=N-1).

    There happens to be only 1 row that meets the START WITH condition, "mgr IS NULL". That is the row with ename='KING'. So 'KING's is the only row with lvl=1, and all of the other rows are in the result set because they are descended from 'KING'. (Note how all the lineage values start with '/KING'.)

    Let's look at a row that entered the result set at LEVEL=2, the row with ename='JONES'. When the PRIOR row was 'KING's, then 'JONES' met the CONNECT BY condition, "mgr = PRIOR empno". 'JONES's mgr is 7839, which is 'KING's empno. So, since 'KING' was on LEVEL=1, 'JONES' is in the result set at LEVEL=1+1 = 2.

    Let's look at another row, the row where ename = 'ADAMS' (the 4th row in the result set). This row entered the result set at LEVEL=4, because it met the CONNECT BY condtion with some other row that was already in the result set at LEVEL = 4-1 = 3. That is, 'SCOTT' was a PRIOR row in the result set at LEVEL=3, and 'ADAMS' met the CONNECT BY condition, so 'ADAMS' is also in the result set, at LEVEL = 3+1 = 4. Looking at the lineage column from right to left, we can see that 'ADAMS' is in the result set because it met the CONNECT BY condition when 'SCOTT' was the PRIOR row, just like 'SCOTT' entered because it met the CONNECT BY condition when 'JONES' was the PRIOR row, and 'JONES' met the condition with respect to 'KING', who was in the result set by meeting the START WITH condition.
  • 16. Re: Connect by
    603257 Journeyer
    Currently Being Moderated
    Start here:

    http://bit.ly/XqXDEq
  • 17. Re: Connect by
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Oracle_Monkey wrote:
    Thanks.

    Ok i got how prior works.

    How does this work
    select level from dual 
    connect by level <11
    Can you give the a bit complex example of LEVEL and explain it.
    There is no START WITH condition, so every row in the table is in the result set at LEVEL=1. There is only 1 row in the dual table, so that is why there is 1 row in the result set with LEVEL=1.
    Now, to get rows at LEVELs 2, 3, 4, and up, we apply the CONNECT BY condition to rows at the PRIOR level.
    When LEVEL=2, what row meets the condition "LEVEL < 11"? Every row does, so every row in the table is included at LEVEL = 2. (Again, there is only 1 row in the table, so there is only 1 row at LEVEL = 2.)
    When LEVEL=3, what row meets the condition "LEVEL < 11"? Every row does, so every row in the table is included at LEVEL = 3.
    ...
    When LEVEL=10, what row meets the condition "LEVEL < 11"? Every row does, so every row in the table is included at LEVEL = 10.
    When LEVEL=11, what row meets the condition "LEVEL < 11"? No row does, so there are no rows in the result set with LEVEL = 11. When the CONNECT BY condition is not met even once, the CONNECT BY query ends.
  • 18. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle_Monkey wrote:
    Thanks.

    Ok i got how prior works.

    How does this work
    select level from dual 
    connect by level <11
    Can you give the a bit complex example of LEVEL and explain it.
    Perhaps if you look at it in terms of some PL/SQL code that may be easier to follow...
    SQL> create or replace procedure get_emp_hierarchy(mgr in number, lvl number := 1) is
      2    cursor get_emps is
      3      select empno, ename, mgr
      4      from   emp
      5      where  nvl(mgr,0) = nvl(get_emp_hierarchy.mgr,0)
      6      order by empno;
      7  begin
      8    --
      9    -- if at the start output some headers
     10    --
     11    if lvl = 1 then
     12      dbms_output.put_line('EMPNO MGR   LVL ENAME');
     13      dbms_output.put_line('----- ----- --- -------------------------');
     14    end if;
     15    --
     16    -- loop through all employees with the specified manager
     17    --
     18    for e in get_emps
     19    loop
     20      --
     21      -- display the current employee
     22      --
     23      dbms_output.put_line(rpad(to_char(e.empno,'fm99999'),5,' ')||' '||
     24                           rpad(nvl(to_char(e.mgr,'fm99999'),' '),5,' ')||' '||
     25                           rpad(to_char(lvl,'fm999'),3,' ')||' '||
     26                           lpad(' ',(lvl-1)*2,' ')||e.ename);
     27      --
     28      -- get all the employees who have this employee as their manager
     29      -- (this is called recursion, and is what creates our hierarchy)
     30      --
     31      get_emp_hierarchy(e.empno -- CONNECT BY MGR = PRIOR EMPNO
     32                       ,lvl+1);
     33    end loop;
     34  end;
     35  /
    
    Procedure created.
    
    SQL> begin
      2    get_emp_hierarchy(null); -- START WITH MGR IS NULL
      3  end;
      4  /
    EMPNO MGR   LVL ENAME
    ----- ----- --- -------------------------
    7839        1   KING
    7566  7839  2     JONES
    7788  7566  3       SCOTT
    7876  7788  4         ADAMS
    7902  7566  3       FORD
    7369  7902  4         SMITH
    7698  7839  2     BLAKE
    7499  7698  3       ALLEN
    7521  7698  3       WARD
    7654  7698  3       MARTIN
    7844  7698  3       TURNER
    7900  7698  3       JAMES
    7782  7839  2     CLARK
    7934  7782  3       MILLER
    
    PL/SQL procedure successfully completed.
    In the above, you can see that the START WITH clause is what we use to tell the process where we want to start in the hierarchy. The CONNECT BY clause is what is used to call the next level of the hierarchy passing the "empno" as the next "mgr" for the next level. The LEVEL is determined by the simply counting depth of the hierarchy, so it get's incremented each level down (in the above example, each time we call the procedure to get the employees with a manager of the current employee).
  • 19. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    and if you want a description to go along with that...
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure get_emp_hierarchy(mgr in number, lvl number := 1) is
      2    cursor get_emps is
      3      select empno, ename, mgr
      4      from   emp
      5      where  nvl(mgr,0) = nvl(get_emp_hierarchy.mgr,0)
      6      order by empno;
      7    padder varchar2(40) := lpad(chr(255),40,chr(255));
      8  begin
      9    --
     10    -- if at the start output some headers
     11    --
     12    dbms_output.put_line(padder||'Process is at level '||lvl);
     13    if lvl = 1 then
     14      dbms_output.put_line('EMPNO MGR   LVL ENAME');
     15      dbms_output.put_line('----- ----- --- --------------------');
     16    end if;
     17    --
     18    -- loop through all employees with the specified manager
     19    --
     20    dbms_output.put_line(padder||'Fetching employees who have a manager of '||nvl(to_char(get_emp_hierarchy.mgr),'NULL'));
     21    for e in get_emps
     22    loop
     23      --
     24      -- display the current employee
     25      --
     26      dbms_output.put_line(padder||'Employee Found: '||e.empno||' ... displaying record:-');
     27      dbms_output.put_line(rpad(to_char(e.empno,'fm99999'),5,' ')||' '||
     28                           rpad(nvl(to_char(e.mgr,'fm99999'),' '),5,' ')||' '||
     29                           rpad(to_char(lvl,'fm999'),3,' ')||' '||
     30                           lpad(' ',(lvl-1)*2,' ')||e.ename);
     31      --
     32      -- get all the employees who have this employee as their manager
     33      -- (this is called recursion, and is what creates our hierarchy)
     34      --
     35      dbms_output.put_line(padder||'Finding all employees with manager: '||e.empno||' (if any)');
     36      get_emp_hierarchy(e.empno -- CONNECT BY MGR = PRIOR EMPNO
     37                       ,lvl+1);
     38    end loop;
     39    dbms_output.put_line(padder||'No (more) employees at level '||lvl||', returning to previous level.');
     40* end;
    SQL> /
    
    Procedure created.
    
    SQL> begin
      2    get_emp_hierarchy(null); -- START WITH MGR IS NULL
      3  end;
      4  /
                                            Process is at level 1
    EMPNO MGR   LVL ENAME
    ----- ----- --- --------------------
                                            Fetching employees who have a manager of NULL
                                            Employee Found: 7839 ... displaying record:-
    7839        1   KING
                                            Finding all employees with manager: 7839 (if any)
                                            Process is at level 2
                                            Fetching employees who have a manager of 7839
                                            Employee Found: 7566 ... displaying record:-
    7566  7839  2     JONES
                                            Finding all employees with manager: 7566 (if any)
                                            Process is at level 3
                                            Fetching employees who have a manager of 7566
                                            Employee Found: 7788 ... displaying record:-
    7788  7566  3       SCOTT
                                            Finding all employees with manager: 7788 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7788
                                            Employee Found: 7876 ... displaying record:-
    7876  7788  4         ADAMS
                                            Finding all employees with manager: 7876 (if any)
                                            Process is at level 5
                                            Fetching employees who have a manager of 7876
                                            No (more) employees at level 5, returning to previous level.
                                            No (more) employees at level 4, returning to previous level.
                                            Employee Found: 7902 ... displaying record:-
    7902  7566  3       FORD
                                            Finding all employees with manager: 7902 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7902
                                            Employee Found: 7369 ... displaying record:-
    7369  7902  4         SMITH
                                            Finding all employees with manager: 7369 (if any)
                                            Process is at level 5
                                            Fetching employees who have a manager of 7369
                                            No (more) employees at level 5, returning to previous level.
                                            No (more) employees at level 4, returning to previous level.
                                            No (more) employees at level 3, returning to previous level.
                                            Employee Found: 7698 ... displaying record:-
    7698  7839  2     BLAKE
                                            Finding all employees with manager: 7698 (if any)
                                            Process is at level 3
                                            Fetching employees who have a manager of 7698
                                            Employee Found: 7499 ... displaying record:-
    7499  7698  3       ALLEN
                                            Finding all employees with manager: 7499 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7499
                                            No (more) employees at level 4, returning to previous level.
                                            Employee Found: 7521 ... displaying record:-
    7521  7698  3       WARD
                                            Finding all employees with manager: 7521 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7521
                                            No (more) employees at level 4, returning to previous level.
                                            Employee Found: 7654 ... displaying record:-
    7654  7698  3       MARTIN
                                            Finding all employees with manager: 7654 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7654
                                            No (more) employees at level 4, returning to previous level.
                                            Employee Found: 7844 ... displaying record:-
    7844  7698  3       TURNER
                                            Finding all employees with manager: 7844 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7844
                                            No (more) employees at level 4, returning to previous level.
                                            Employee Found: 7900 ... displaying record:-
    7900  7698  3       JAMES
                                            Finding all employees with manager: 7900 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7900
                                            No (more) employees at level 4, returning to previous level.
                                            No (more) employees at level 3, returning to previous level.
                                            Employee Found: 7782 ... displaying record:-
    7782  7839  2     CLARK
                                            Finding all employees with manager: 7782 (if any)
                                            Process is at level 3
                                            Fetching employees who have a manager of 7782
                                            Employee Found: 7934 ... displaying record:-
    7934  7782  3       MILLER
                                            Finding all employees with manager: 7934 (if any)
                                            Process is at level 4
                                            Fetching employees who have a manager of 7934
                                            No (more) employees at level 4, returning to previous level.
                                            No (more) employees at level 3, returning to previous level.
                                            No (more) employees at level 2, returning to previous level.
                                            No (more) employees at level 1, returning to previous level.
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Edited by: BluShadow on 19-Feb-2013 14:22
    improved on description
  • 20. Re: Connect by
    APC Oracle ACE
    Currently Being Moderated
    YYou do undersatnd what a hirearchy is, right? It's a mechanism for grouping things according to their relative rank.

    So if the army, all sargeants are equal, they are all junior to captains and all senior to privates. So that's what LEVEL is, a number assigned to each discrete rank in the hierachy, with 1 as the toppermost rank, the record defined by START WITH.

    That piece of code is contentious, because it is strictly undocumented behaviour. Even in [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#sthref6773]the 11g SQL Reference it still says:
    "CONNECT BY Clause ... must use the PRIOR operator to refer to the parent row."
    Some people interpret that to mean that this is unfit for production code ...
    select level from dual 
    connect by level <11
    ... because it is a CONNECT BY without a PRIOR. Other people take a more pragmatic view, which is that it is a highly neat (and performant) mechanism for generating rows.

    Cheers, APC
  • 21. Re: Connect by
    APC Oracle ACE
    Currently Being Moderated
    BluShadow wrote:
    and if you want a description to go along with that...
    Hmmm, it must be lunchtime at chez Blu. Or should that be chez Bleu ?

    Cheers, APC
  • 22. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    APC wrote:
    YYou do undersatnd what a hirearchy is, right? It's a mechanism for grouping things according to their relative rank.

    So if the army, all sargeants are equal, they are all junior to captains and all senior to privates. So that's what LEVEL is, a number assigned to each discrete rank in the hierachy, with 1 as the toppermost rank, the record defined by START WITH.

    That piece of code is contentious, because it is strictly undocumented behaviour. Even in [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#sthref6773]the 11g SQL Reference it still says:
    "CONNECT BY Clause ... must use the PRIOR operator to refer to the parent row."
    Yes, you must use a prior operator to refer to the parent row... but if you're not actually referring to the parent row (data) you don't need the prior. ;)
    Some people interpret that to mean that this is unfit for production code ...
    select level from dual 
    connect by level <11
    ... because it is a CONNECT BY without a PRIOR. Other people take a more pragmatic view, which is that it is a highly neat (and performant) mechanism for generating rows.
    Yep, I take it that way. ;)
  • 23. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    APC wrote:
    BluShadow wrote:
    and if you want a description to go along with that...
    Hmmm, it must be lunchtime at chez Blu. Or should that be chez Bleu ?
    LOL! Yeah, I just thought a recursive procedure with description may give a clue to the OP as to how a hierarchy is processed.
  • 24. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    what does
    prior empno as prior_empno
    this mean
  • 25. Re: Connect by
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Oracle_Monkey wrote:
    what does
    prior empno as prior_empno
    this mean
    In a SELECT clause:
    SELECT  ...
            e       AS a
    means that the expression e will be in the result set, and it will be called by the alias a.
    So, in this case
    SELECT  ...
            PRIOR empno       AS prior_empno
    the value of the expression <tt> PRIOR empno </tt> (that is, empno from the priior node, to which the current row is connected) will be included in the result set, as a column called prior_empno. The alias is not being used in Blushadow's query ( {message:id=10857349} ) itself, but your front-end may use it when displaying results.
  • 26. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    yes i got it.

    can u tell how to display data to certain level?
    like from level 1 to level 3 .
    what should be the query to display level 3 data
  • 27. Re: Connect by
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Oracle_Monkey wrote:
    yes i got it.

    can u tell how to display data to certain level?
    like from level 1 to level 3 .
    what should be the query to display level 3 data
    The WHERE clause is evaluated after the START WITH and CONNECT BY clauses have been applied, so you can say:
    WHERE   LEVEL   = 3
    If you want only rows from LEVEL 3.
    If you want LEVELs 1 through 3, then you could use a WHERE clause, for example:
    WHERE   LEVEL   BETWEEN 1 AND 3
    but it would be more efficient to do that in the CONNECT BY clause:
    CONNECT BY   mgr    = PRIOR empno   -- or whatever
            AND  LEVEL  <= 3
    There's no reason to fetch the rows at levels 4, 5, 6, ... if you know you don't want them.
  • 28. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle_Monkey wrote:
    what does
    prior empno as prior_empno
    this mean
    As already mentioned, this wasn't used by the query, but was just output in the results so that you could see how the prior empno was the same as the mgr.
  • 29. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    Thank you all for the detailed explanation.Picture is clear now.the concept of level is important in understanding connect by.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points