Hi all,
I have a recursive query to be written in db2, fow which referring to IBM doc I wrote the following query. The output I need is to on entering the emp_code, all the employees wrking under it recursively. The problem is after running the query the server goes in hang state and the progress bar just goes on, Can anyone help out with the cause ??


WITH RPL (empcode, functional_reporting_to) AS
(

SELECT ROOT.functional_reporting_to, ROOT.empcode
FROM db2admin.employee_details ROOT
WHERE ROOT.functional_reporting_to = 'emp500'

UNION ALL

SELECT CHILD.functional_reporting_to, CHILD.empcode
FROM RPL PARENT, db2admin.employee_details CHILD
WHERE PARENT.empcode = CHILD.functional_reporting_to
and child.functional_reporting_to = 'emp500'


)

SELECT DISTINCT functional_reporting_to, empcode
FROM RPL
ORDER BY functional_reporting_to, empcode

[...]
UNION ALL

SELECT CHILD.functional_reporting_to, CHILD.empcode
FROM RPL PARENT, db2admin.employee_details CHILD
WHERE PARENT.empcode = CHILD.functional_reporting_to
and child.functional_reporting_to = 'emp500'
[...]

This condition is erroneous.
employees, which are added in the recursion do not report to emp500. They report to employees which report to employees which report to employees which ... report to emp500.

Just omit this condition.


You might want to include an counter to limit the number of iterations ( to avoid infinite loops ):



WITH RPL (empcode, functional_reporting_to, level) AS
(
SELECT ROOT.functional_reporting_to, ROOT.empcode , 1 as level
FROM db2admin.employee_details ROOT
WHERE ROOT.functional_reporting_to = 'emp500'

UNION ALL

SELECT CHILD.functional_reporting_to, CHILD.empcode, parent.level + 1 as level
FROM RPL PARENT, db2admin.employee_details CHILD
WHERE PARENT.empcode = CHILD.functional_reporting_to
and parent.level < 10
)
SELECT DISTINCT functional_reporting_to, empcode, level
FROM RPL
ORDER BY functional_reporting_to, empcode ;

Additional to that, I thought column names should be changed to the following.
WITH RPL (functional_reporting_to, empcode) AS