1 2 Previous Next 29 Replies Latest reply on Feb 20, 2013 9:43 AM by Oracle_Monkey

    Connect by

    Oracle_Monkey
      How does connect by works?
      Need an example
        • 2. Re: Connect by
          Oracle_Monkey
          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.
              1 person found this helpful
              • 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
                      Oracle_Monkey
                      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
                        Oracle_Monkey
                        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
                            Oracle_Monkey
                            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...
                              1 person found this helpful
                              • 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
                                  Oracle_Monkey
                                  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