1 2 Previous Next 18 Replies Latest reply: Dec 13, 2012 5:59 AM by Ryansun-Oracle Go to original post RSS
      • 15. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
        AlbertoFaenza
        Hi Ryan,

        I think that if top manager has manager_id = employee_id is causing:
        ORA-01436: CONNECT BY loop in user data
        You can remove this problem using
        CONNECT BY NOCYCLE
        but your top manager is not going to be shown in your output.

        You can try it by yourself and see the problem.

        Regards.
        Al
        • 16. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
          Ryansun-Oracle
          Hi Alberto,

          Yes, I did try out a couple of options.

          In my case, the query executes without any errors, just that the top level manager does not show. I was supposed to show the hierarchy top to bottom

          Now my machine is up for formatting. Will try again tomorrow without the the cycle option

          Thanks,
          Ryan
          • 17. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
            AlbertoFaenza
            Hi Ryan,

            I did a test by myself forcing employee_id in manager_id field when it is null and I can confirm that is returning the error.

            See below:
            WITH emp2 AS
            (
               SELECT employee_id, first_name, last_name, email
                     , phone_number, hire_date, job_id, salary, commission_pct
                     -- forcing employee_id when manager_id is null
                     , NVL(manager_id, employee_id) manager_id 
                     , department_id
                 FROM employees
            )
            , entire_tree AS
            (
                SELECT *
                  FROM emp2
                 START WITH manager_id = 121
               CONNECT BY PRIOR employee_id = manager_id
               UNION
                SELECT *
                  FROM emp2
                 START WITH employee_id = 121
               CONNECT BY employee_id = PRIOR manager_id
            )
             SELECT employee_id, manager_id
                  , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 0 WHEN LEVEL = 1 THEN 1 ELSE -1 END AS status
                  , LEVEL
                  , first_name AS title
                  , NULL AS icon
                  , employee_id AS VALUE
                  , first_name AS tooltip
                  , NULL AS link
               FROM entire_tree
              START WITH manager_id IS NULL
            CONNECT BY PRIOR employee_id = manager_id
              ORDER SIBLINGS BY first_name
            *
            Error at line 0
            ORA-01436: CONNECT BY loop in user data
            It's the same error if I change the start
              START WITH manager_id = 100
            Conceptually it is not correct having manager_id = employee_id. Personally when they are equal I would set in input my manager_id to NULL to avoid loops.

            i.e.:
            WITH emp2 AS
            (
               SELECT employee_id, first_name, last_name, email
                     , phone_number, hire_date, job_id, salary, commission_pct
                     , CASE 
                       WHEN manager_id = employee_id 
                          THEN NULL 
                       ELSE manager_id 
                       END AS manager_id  
                     , department_id
                 FROM employees
            )
            , entire_tree AS
            (
                SELECT *
                  FROM emp2
                 START WITH manager_id = 121
               CONNECT BY PRIOR employee_id = manager_id
               UNION
                SELECT *
                  FROM emp2
                 START WITH employee_id = 121
               CONNECT BY employee_id = PRIOR manager_id
            )
             SELECT CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 0 WHEN LEVEL = 1 THEN 1 ELSE -1 END AS status
                  , LEVEL
                  , first_name AS title
                  , NULL AS icon
                  , employee_id AS VALUE
                  , first_name AS tooltip
                  , NULL AS link
               FROM entire_tree
              START WITH manager_id IS NULL
            CONNECT BY PRIOR employee_id = manager_id
              ORDER SIBLINGS BY first_name;
            
                STATUS      LEVEL TITLE                ICON      VALUE TOOLTIP              LINK
            ---------- ---------- -------------------- ---- ---------- -------------------- ----
                     1          1 Steven                           100 Steven                   
                    -1          2 Adam                             121 Adam                     
                     0          3 Alexis                           185 Alexis                   
                     0          3 Anthony                          187 Anthony                  
                     0          3 James                            131 James                    
                     0          3 Julia                            186 Julia                    
                     0          3 Laura                            129 Laura                    
                     0          3 Mozhe                            130 Mozhe                    
                     0          3 Nandita                          184 Nandita                  
                     0          3 TJ                               132 TJ                       
            Regards.
            Al
            • 18. Re: Start with Connect by: Showing the whole hierarchy even if child parameter
              Ryansun-Oracle
              Thanks Alberto! I like the idea of using Case to force the Manager id to null. Unfortunately, I can try this only tomorrow. will get back with my results.

              Thanks,
              Ryan
              1 2 Previous Next