# Recursive Manager - Oracle 10g x 11g

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

SQL Fiddle

• Hello Balazs Papp, Thanks for the answer, that is something we will consider. The only problem for us, is that we are trying to use the SQL as View and then filter the view, due to our application must support other databases. Do you have any other ideas? Is that possible on Oracle 10g? Commented Jul 13, 2015 at 21:03

Reference: Oracle Forum

``````WITH ADRECURSIVEUSERLEADER
AS
(