1 2 Previous Next 29 Replies Latest reply: Feb 20, 2013 3:43 AM by 980503 Go to original post RSS
      • 15. Re: Connect by
        Frank Kulash
        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
          Start here:

          http://bit.ly/XqXDEq
          • 17. Re: Connect by
            Frank Kulash
            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
              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
                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
                  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
                    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
                      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
                        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
                          what does
                          prior empno as prior_empno
                          this mean
                          • 25. Re: Connect by
                            Frank Kulash
                            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
                              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
                                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
                                  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
                                    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