Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2008
    Posts
    40

    Unanswered: Recursive query help required !!!!

    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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    [...]
    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 ;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    and child.functional_reporting_to = 'emp500'
    [...]

    This condition is erroneous.
    .....
    .....

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •