Recursive CTE with 2 tables not working in Oracle / SQL

debugcn Published at Dev

Emil Joshva

There are 2 tables Department and subdepartment which have id in common. I am trying to recursively fetch all the ids reporting to AB directly and indirectly. BC is reporting to AB, hence 4,5,6 are indirectly reporting to AB, likewise fetching till the last id.

Tried the below recursive CTE query but I am getting the result of only the first level. Seems recursive query is not executing.

I am not sure what is wrong in the query. Can someone help me in spotting the error.

Department

Name     id
AB          1
AB          2
AB          3
BC          4
BC          5
BC          6
CD          7
CD          8
EF          9
EF         10
EF         11

Subdepartment

ID      Reporting
1
2
3         BC
4
5         CD
6
7
8         EF
9
10
11

Query:

With reportinghierarchy (Name, Id, Reporting, Level) As
(

--Anchor
Select A.name,A.id,reporting,0 from department A, subdepartment B
where A.id=B.id and A.name='AB'

Union All

--Recursive member
Select C.name,C.id,D.reporting, Level+1 from department C, subdepartment D
Inner Join  reportinghierarchy R
On (C.Name = R.reporting)
Where C.name != 'AB' and C.Id =D.id
And R.Reporting is not null
)
Select * from reportinghierarchy

Current Output :

Name Id  Reporting  Level
AB   1                0
AB   2                0
AB   3   BC           0

Expected output :

Name     id     Reporting  Level
AB        1                  0
AB        2                  0
AB        3       BC         0
BC        4                  1
BC        5       CD         1
BC        6                  1
CD        7                  2
CD        8       EF         2
EF        9                  3
EF       10                  3
EF       11                  3

Bob Jarvis - Reinstate Monica

Your original query was actually VERY, VERY close to working. The reasons it didn't work are:

You used the keyword LEVEL as a column name without quoting it. In Oracle LEVEL has specific meaning, and using it out of context causes the parser no end of headaches. I've changed it to LVL, which works fine.
In the recursive half of the UNION you mixed old-style and new-style joins. This is a huge problem and should never be done. Either use "old-style" implied joins, or use "new-style" explicit joins. To keep as close to your original as possible I used implicit joins, but good coding practice says you should use explicit joins all the time.

The corrected query is:

With reportinghierarchy (Name, Id, Reporting, lvl) As
(

--Anchor
Select A.name,A.id,reporting,0 from department A, subdepartment B
where A.id=B.id and A.name='AB'

Union All

--Recursive member
Select C.name,C.id,D.reporting, lvl+1 from department C, subdepartment D, reportinghierarchy R
Where C.name != 'AB' and C.Id =D.id and C.Name = R.reporting
And R.Reporting is not null
)
Select * from reportinghierarchy;

Given the above, the following results are returned, which appear to match your desired results:

NAME    ID  REPORTING   LVL
AB      1   (null)      0
AB      2   (null)      0
AB      3   BC          0
BC      4   (null)      1
BC      5   CD          1
BC      6   (null)      1
CD      7   (null)      2
CD      8   EF          2
EF      9   (null)      3
EF      10  (null)      3
EF      11  (null)      3

SQLFiddle here

Best of luck.

Collected from the Internet

Please contact [email protected] to delete if infringement.