2

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

1

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

  • 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? – Scudeler Jul 13 '15 at 21:03
0

Reference: Oracle Forum

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.