This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Feb 20, 2013 1:43 AM by 980503 RSS

Connect by

980503 Newbie
Currently Being Moderated
How does connect by works?
Need an example
  • 1. Re: Connect by
    jeneesh Guru
    Currently Being Moderated
  • 2. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    Their example suck.Its too complicated
  • 3. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle_Monkey wrote:
    Their example suck.Its too complicated
    Really? I thought it was quite simple.... with pretty diagrams and everything too.

    Is this simple enough for you to follow...
    SQL> ed
    Wrote file afiedt.buf
    
      1  select empno
      2        ,level as lvl
      3        ,prior empno as prior_empno
      4        ,mgr
      5        ,rpad(' ',(level-1)*2,' ')||ename as ename
      6  from emp
      7  connect by mgr = prior empno
      8* start with mgr is null
    SQL> /
    
         EMPNO        LVL PRIOR_EMPNO        MGR ENAME
    ---------- ---------- ----------- ---------- ------------------------------
          7839          1                        KING
          7566          2        7839       7839   JONES
          7788          3        7566       7566     SCOTT
          7876          4        7788       7788       ADAMS
          7902          3        7566       7566     FORD
          7369          4        7902       7902       SMITH
          7698          2        7839       7839   BLAKE
          7499          3        7698       7698     ALLEN
          7521          3        7698       7698     WARD
          7654          3        7698       7698     MARTIN
          7844          3        7698       7698     TURNER
          7900          3        7698       7698     JAMES
          7782          2        7839       7839   CLARK
          7934          3        7782       7782     MILLER
    
    14 rows selected.
  • 4. Re: Connect by
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Some other tutorials on CONNECT BY are:

    http://philip.greenspun.com/sql/trees.html
    http://www.adp-gmbh.ch/ora/sql/connect_by.html

    I'm not sure you'll like them any better, though; this is not the simplest feature in Oracle.
  • 5. Re: Connect by
    Sg049 Explorer
    Currently Being Moderated
    Some queries are missing "Start with" clause or you can write a query with out "Start with" clause. How that logic works?

    Select 1 from dual connect by level <=10;
    
    select count(*) NUM_OF_ROWS  from EMP ;
    
    NUM_OF_ROWS
    ----------------------
    14
    
    select count(*) NUM_OF_ROWS from EMP
    connect by level <=1;
    
    NUM_OF_ROWS
    ----------------------
    14
    
    select count(*) from EMP
    connect by level <=2;
    
    NUM_OF_ROWS
    ----------------------
    210
    
    select count(*) NUM_OF_ROWS from EMP
    connect by level <=3;
    NUM_OF_ROWS
    ----------------------
    2954
    what is the logic or formula for these queries? Could you explain me?
  • 6. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    please don't hijack other people's questions with your own, start your own thread instead.
    Sg049 wrote:
    Some queries are missing "Start with" clause or you can write a query with out "Start with" clause. How that logic works?
    In such cases it will use each row as a starting point and give you all the possibily hierarchies available, based on the conditions of the query.
    Select 1 from dual connect by level <=10;
    Hierarchical queries generate levels, and the level in this case is being used as the hierarchical condition, so it will keep on going down to the next level until level <= 10 is met. It is implied that level starts at 1.
  • 7. Re: Connect by
    Sg049 Explorer
    Currently Being Moderated
    Thanks Blu for explanation...

    I was thinking that if i ask this question here so the OP also can understand it well and others can get all the related information in one thread. From now on i will post in separate thread.
  • 8. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    Frank Kulash wrote:
    Hi,

    Some other tutorials on CONNECT BY are:
    Frank thank you
    Can you explain one example with explanation.

    >
    I'm not sure you'll like them any better, though; this is not the simplest feature in Oracle.
    Frank be nice ..lol
  • 9. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    BluShadow wrote:
    Oracle_Monkey wrote:
    Their example suck.Its too complicated
    Really? I thought it was quite simple.... with pretty diagrams and everything too.
    Blu i didnt got the flow your query. i mean any connect by query
  • 10. Re: Connect by
    BluShadow Guru Moderator
    Currently Being Moderated
    Oracle_Monkey wrote:
    BluShadow wrote:
    Oracle_Monkey wrote:
    Their example suck.Its too complicated
    Really? I thought it was quite simple.... with pretty diagrams and everything too.
    Blu i didnt got the flow your query. i mean any connect by query
    Well, it's one of the simplest hierarchical queries there is, showing employees and their managers.
    Unless you tell us which bit you're not understanding, we're really going to struggle to help you.
  • 11. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    Ho does this works
    connect by mgr = prior empno
  • 12. Re: Connect by
    jeneesh Guru
    Currently Being Moderated
    Oracle_Monkey wrote:
    Ho does this works
    connect by mgr = prior empno
    It is not that complicated..Look at the query given by Blu
    BluShadow wrote:
    SQL> ed
    Wrote file afiedt.buf
    
    1  select empno
    2        ,level as lvl
    3        ,prior empno as prior_empno
    4        ,mgr
    5        ,rpad(' ',(level-1)*2,' ')||ename as ename
    6  from emp
    7  connect by mgr = prior empno
    8* start with mgr is null
    SQL> /
    
    EMPNO        LVL PRIOR_EMPNO        MGR ENAME
    ---------- ---------- ----------- ---------- ------------------------------
    7839          1                        KING
    7566          2        7839       7839   JONES
    7788          3        7566       7566     SCOTT
    7876          4        7788       7788       ADAMS
    7902          3        7566       7566     FORD
    7369          4        7902       7902       SMITH
    7698          2        7839       7839   BLAKE
    7499          3        7698       7698     ALLEN
    7521          3        7698       7698     WARD
    7654          3        7698       7698     MARTIN
    7844          3        7698       7698     TURNER
    7900          3        7698       7698     JAMES
    7782          2        7839       7839   CLARK
    7934          3        7782       7782     MILLER
    
    14 rows selected.
    You need to see the "START WITH" clause first.

    It is saying "START WITH mgr is null" (it is as simple as saying start with the CEO).

    So you will check which record has "MGR is null" - You get the row of the emp no 7839 - That is KING .At this time the only record you have is of empno 7839. This the record with LEVEL as 1

    Now see the next line of the code (Which will define the next row of your output) - "connect by mgr = prior empno". Think in pure English - It is saying Next row is that for which previous empno (here KING) is the manager (ie., all the rows for which MGR = 7839)

    So, you will get the rows with EMPNO - 7566,7698,7782. These are LEVEL2 records..

    Now, you will apply the same rule - So you will get all the employees whose MGR is any one of the above - These are LEVEL3 ..

    This continues...
  • 13. Re: Connect by
    APC Oracle ACE
    Currently Being Moderated
    Oracle_Monkey wrote:
    Ho does this works
    connect by mgr = prior empno
    MGR is the identifier (EMPNO) for the current employee's manager's record, and is a foreign key to it

    So that code snippet means join the EMP records which have a value in the MGR column matching the EMPNO of the immediately previous record at the previous level.

    Which sounds yuck but that's the problem which hierarchical queries. They are easy to draw or to code than to explain in lay English.

    Cheers, APC
  • 14. Re: Connect by
    980503 Newbie
    Currently Being Moderated
    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.
1 2 Previous Next

Legend

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