# 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
(
,US.CDUSER AS CDUSER
,US.NMUSER
,US.FGUSERENABLED
WHERE 1 = 1
UNION ALL
,US.CDUSER
,US.NMUSER
,US.FGUSERENABLED
WHERE 1 = 1
)
SELECT *
WHERE 1 = 1;


Oracle 10g Query ( What we are trying )

WITH ADRECURSIVEUSERLEADER
AS
(
,US.CDUSER AS CDUSER
,US.NMUSER
,US.FGUSERENABLED
CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT *
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) 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)
,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 (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

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
(