This discussion is archived
9 Replies Latest reply: Dec 13, 2012 12:31 PM by Frank Kulash RSS

Connect By - Without Start Query

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

    Cheers,
    Manik.
  • 2. Re: Connect By - Without Start Query
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    ;

Legend

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