1 2 Previous Next 29 Replies Latest reply: Feb 20, 2013 3:43 AM by 980503 RSS

    Connect by

    980503
      How does connect by works?
      Need an example
        • 1. Re: Connect by
          jeneesh
          • 2. Re: Connect by
            980503
            Their example suck.Its too complicated
            • 3. Re: Connect by
              BluShadow
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Ho does this works
                              connect by mgr = prior empno
                              • 12. Re: Connect by
                                jeneesh
                                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_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
                                    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