Recursive Manager – Oracle 10g x 11g

oracle-10grecursive

We have a problem adapting a recursive SQL from 11g to 10g.

Oracle 11g Query ( Working as expected )

WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED)
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     WHERE 1 = 1
     UNION ALL
    SELECT RS.CDLEADER
          ,US.CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADRECURSIVEUSERLEADER RS
           INNER JOIN ADUSER US 
               ON RS.CDUSER = US.CDLEADER
     WHERE 1 = 1
)
SELECT * 
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

Oracle 10g Query ( What we are trying )

WITH ADRECURSIVEUSERLEADER 
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     START WITH US.CDLEADER IS NULL         -- parent
   CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT * 
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

What is the problem?

We have the following leader structure:

  • User code (CDUSER) 5 is the CEO
  • User code (CDUSER) 21 is the Manager (Leaded by 5)
  • User code (CDUSER) 937 is the Leader (Leaded by 21)
  • User code (CDUSER) 1276 is the Developer (Leaded by 937)

If I search for a leader on 10g and 11g :

AND T.CDLEADER = 21

11g shows me two records:

  • One for 937 ( Leader )
  • One for 1276 ( Developer )

10g shows me only one record:

  • One for 937 ( Leader )

SQL Fiddle Example

DDL for Oracle:

CREATE TABLE ADUSER (CDUSER          NUMBER(10)
                    ,CDLEADER        NUMBER(10)
                    ,FGUSERENABLED   NUMBER(2)
                    ,NMUSER          VARCHAR2(255) );

INSERT INTO ADUSER VALUES (   5, NULL, 1, 'CEO Name');
INSERT INTO ADUSER VALUES (  21,    5, 1, 'Manager Name');
INSERT INTO ADUSER VALUES ( 937,   21, 1, 'Leader Name');
INSERT INTO ADUSER VALUES (1276,  937, 1, 'Developer Name');

What I need?

I need to know everyone under the leader I search and the filter must me apllied on the (ADRECURSIVEUSERLEADER T) because it's going to be a view . Is that possible on Oracle 10g?

Thanks a lot

Best Answer

If you want to find the people "under" CDLEADER = 21, then why do use START WITH US.CDLEADER IS NULL in your CONNECT BY? Just simply use START WITH US.CDLEADER = 21:

WITH ADRECURSIVEUSERLEADER 
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     START WITH US.CDLEADER = 21         -- parent
   CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT * 
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

SQL Fiddle