9 Replies Latest reply: Dec 13, 2012 2:31 PM by Frank Kulash RSS

    Connect By - Without Start Query

    CP
      Is it possible to get the hierarchy properly in 'Connect By' without using 'Start With' Query ?
        • 1. Re: Connect By - Without Start Query
          Manik
          You may check recursive with clause in oracle 11g r2.

          Cheers,
          Manik.
          • 2. Re: Connect By - Without Start Query
            Mahir M. Quluzade
            Hi,

            Yes you use Connect by without START WITH
             
              select level  from  dual connect by  level <=10; 
             
            Regards
            Mahir M. Quluzade
            • 3. Re: Connect By - Without Start Query
              Purvesh K
              CP wrote:
              Is it possible to get the hierarchy properly in 'Connect By' without using 'Start With' Query ?
              Yes, why not. If you have only Two Records in your table. One parent and Other is child.

              Without a START WITH clause, every row is considered the Root and the is processed according to the connect by condition.

              try this:
              select *
                from emp
              start with empno = 7788
              connect by empno = prior mgr;
              AND
              select *
                from emp
              --start with empno = 7788
              connect by empno = prior mgr;
              Comparing the results will explain how Connect By works with or without Start with.
              • 4. Re: Connect By - Without Start Query
                CP
                Purvesh K Wrote:
                Yes, why not. If you have only Two Records in your table. One parent and Other is child.

                Without a START WITH clause, every row is considered the Root and the is processed according to the connect by condition
                >

                But this is not the case. If I have more records?
                • 5. Re: Connect By - Without Start Query
                  Purvesh K
                  CP wrote:

                  But this is not the case. If I have more records?
                  I said earlier, Each record shall be treated as Root and processed in the Hierarchy according to the Connect By conditions. For this reason, you have to provide the Start With condition.
                  • 6. Re: Connect By - Without Start Query
                    CP
                    I am using 'Connect By' Query in a join Condition.
                    SELECT employee_id
                       FROM employee e, dept d
                       WHERE e.dept_id = d.dept_id
                       AND e.employee_id = 100
                       START WITH manager_id IS NULL
                       CONNECT BY PRIOR employee_id = manager_id;
                    Manager ID may or may not be there. If manager id is NOT NULL then this query will fail. So need Connect By Query without Start Clause.
                    • 7. Re: Connect By - Without Start Query
                      AlbertoFaenza
                      CP wrote:
                      I am using 'Connect By' Query in a join Condition.
                      SELECT employee_id
                      FROM employee e, dept d
                      WHERE e.dept_id = d.dept_id
                      AND e.employee_id = 100
                      START WITH manager_id IS NULL
                      CONNECT BY PRIOR employee_id = manager_id;
                      Manager ID may or may not be there. If manager id is NOT NULL then this query will fail. So need Connect By Query without Start Clause.
                      You seem a bit confused about hierarchical queries.
                      Let me put it in this way: hierarchical queries are returning rows from a table climbing from root to branches(or leaves if you prefer) or descending from a branch (or leaf) to a root.

                      When you start you have to say where you start from. This can be any value in your table.
                      I will use here below the standard table employees from HR schema provided with Oracle.

                      Suppose that you want to select top-down hierarchy (or root-leaves if you prefer) starting from employees_id=108.
                      In this case you can do like this:
                       SELECT employee_id, manager_id
                         FROM employees e
                        START WITH employee_id=108
                      CONNECT BY PRIOR employee_id = manager_id;
                      
                      EMPLOYEE_ID MANAGER_ID
                      ----------- ----------
                              108        101
                              109        108
                              110        108
                              111        108
                              112        108
                              113        108
                      Now, you see that employee_id 108 is also a manager. If I change my query and I say that I want to start with manager_id=108 I will start from records having manager_id=108. Therefore employee_id=108 will be excluded:
                       SELECT employee_id, manager_id
                         FROM employees e
                        START WITH manager_id=108
                      CONNECT BY PRIOR employee_id = manager_id;
                      
                      EMPLOYEE_ID MANAGER_ID
                      ----------- ----------
                              109        108
                              110        108
                              111        108
                              112        108
                              113        108
                      If you don't specify a START WITH clause, for every record in your table the top-down hierarchical query will be executed.
                       SELECT employee_id, manager_id
                         FROM employees e
                      --  START WITH employee_id=100
                      CONNECT BY PRIOR employee_id = manager_id
                      order by employee_id;
                      EMPLOYEE_ID MANAGER_ID
                      ----------- ----------
                              100           
                              101        100
                              101        100
                              102        100
                              102        100
                      ....
                              206        205
                              206        205
                              206        205
                              206        205
                      
                      315 rows selected.
                      I hope it is clear. If not please post additional details about your question.

                      Regards.
                      Al
                      • 8. Re: Connect By - Without Start Query
                        Frank Kulash
                        Hi,
                        CP wrote:
                        I am using 'Connect By' Query in a join Condition.
                        SELECT employee_id
                        FROM employee e, dept d
                        WHERE e.dept_id = d.dept_id
                        AND e.employee_id = 100
                        START WITH manager_id IS NULL
                        CONNECT BY PRIOR employee_id = manager_id;
                        That looks good syntactically. Whether it does what you want or not, I can't say, since I don't know what you want.
                        Manager ID may or may not be there. If manager id is NOT NULL then this query will fail.
                        That depends on what you mean by "fail". Is the query above not doing what you want? Post CREATE TABLE and INSERT statments for some sample data, and the results you want from that data.
                        So need Connect By Query without Start Clause.
                        Sometimes you do need CONNECT BY without START BY. I don't know what you're trying to do, so I don;'t know if this is one of those cases or not.
                        The START WITH clause restricts what rows are on LEVEL=1. If there is no START WITH clause, then there are no restrictions, and all rows will appear on LEVEL=1. (CONNECT BY is done after joins, so you'll get 0 rows in the result set if the join condition is never TRUE.)
                        • 9. Re: Connect By - Without Start Query
                          Frank Kulash
                          Hi,

                          Perhaps this will help you see why sometimes you want a START WITH clause, and sometimes you don't.

                          Let's say we want to get output like this, that reflects the tree structure of scott.emp:
                          INDENTED_ENAME         SAL DESCENDANT_SAL
                          -------------------- ----- --------------
                          KING                  5000          29025
                            JONES               2975          10875
                              FORD              3000           3800
                                SMITH            800            800
                              SCOTT             3000           4100
                                ADAMS           1100           1100
                            BLAKE               2850           9400
                              ALLEN             1600           1600
                              WARD              1250           1250
                              JAMES              950            950
                              TURNER            1500           1500
                              MARTIN            1250           1250
                            CLARK               2450           3750
                              MILLER            1300           1300
                          Here, we have to compute descendant_sal, which is the total salary of the employee and all of his subordinates, either directly or indirectly reporting to him. For example, descendant_sal for JONES is 2975 + 3000 + 800 + 3000 + 1100 = 10875. This reflects JONES own sal (2975), the combined sal of JONES's children (FORD, 3000 and SCOTT, 3000), and the combined sal of their children (SMITH, 800 and ADAMS 1100). The indented_name column has to be formatted so that it's easy to see, for example, that ADAMS reports directly to SCOTT and indirectly to JONES and KING.

                          Here's one way we might get those results:
                          WITH     got_descendant_sal          AS
                          (
                               SELECT       empno, mgr, ename, sal
                               ,       SUM (CONNECT_BY_ROOT  sal)     AS descendant_sal
                               FROM       scott.emp
                               -- No START WITH clause needed here
                               CONNECT BY  empno  = PRIOR mgr
                               GROUP BY    empno, mgr, ename, sal
                          )
                          SELECT     LPAD ( ' '
                                    , 2 * (LEVEL - 1)
                                    ) || ename          AS indented_ename
                          ,       sal
                          ,     descendant_sal
                          FROM     got_descendant_sal
                          START WITH  mgr     IS NULL     -- START WITH clause needed here
                          CONNECT BY  mgr         = PRIOR empno
                          ;
                          As you can see, in the sub-query we want to treat each person as a root, so there is no START WITH clause in the sub-query. In the main query, we only want the people who have no boss to be considered roots, so there is a START WITH clause in the main query.

                          If this query (or any query) is hard to understand, try to reconstruct the query step by step, and make sure you understand each step. In this case, you might take the sub-query got_descendants as a frist step, but that sub-query is really doing 2 steps (CONNECT BY first, then GROUP BY), so let's just run the sub-query without the GROUP BY.
                          Also, when trying to understand or debug CONNECT BY queries, it's often useful to show the path of parent-child connections that brought us to each row, so let's include SYS_CONNECT_BY_PATH in the results:
                          -- simplified got_descendants, just for dissection
                          
                               SELECT       empno, mgr, ename, sal
                               ,       CONNECT_BY_ROOT  sal               AS descendant_sal
                               ,       SYS_CONNECT_BY_PATH (ename, ',')     AS path
                               FROM       scott.emp
                               -- No START WITH clause needed here
                               CONNECT BY  empno  = PRIOR mgr
                          --      GROUP BY    empno, mgr, ename, sal
                          ;