Re: A CTE Question

From: Erland Sommarskog <esquel_at_sommarskog.se>
Date: Sun, 29 Aug 2010 13:52:09 +0200
Message-ID: <Xns9DE38D16221C6Yazorman_at_127.0.0.1>

Erland Sommarskog (esquel_at_sommarskog.se) writes:
> Having r.dep_id in the recursive part might be a mistake, but it should
> not cause infinite recursion. The recursion is controlled by the WHERE
> clause, and the WHERE does not involve r.dep_id.

>... 

>
> Not that I think the output is useful for the problem Michel presented,
> but I like to point out that there are other situations where this result
> is useful, at least if you remove all rows with PARENT = -2. To with, this
> table gives us a quick answer to "does subdept n belong dept m directly
> or indirectly".
 

I was wrong. I was prompted to study the solutions in the DB2 newsgroup, and the CTE Michel had is in fact quite close to the solution. He had:

     select dep_id, parent, 0 from DEPARTMENT
     union all
     select r.dep_id, 
            d.parent, iter + 1
     from  t_recurs r, DEPARTMENT d
     where r.parent = d.dep_id and iter < 1000

The change needed is not in the recursive part, but in the root. It needs to read:

     select dep_id, dep_id, 0 from DEPARTMENT

Yes, dep_id twice! This CTE results in a table similar to the one I posted yesterday. That is, you can see which departments a certain department is a member, but now there is also a row where the department is a member of itself.

And once you have this it's simple to join on department level to PERSON, but aggregate on the enclosing aggregate:

   with tc (dep_id, ancestor_id, iter) as (

       select DEP_ID, DEP_ID, 0 from DEPARTMENT
       union all
       select tc.dep_id, d.PARENT, iter + 1
       from  tc, DEPARTMENT d
       where tc.ancestor_id = d.DEP_ID
         and d.PARENT >= 0

   )
   select tc.ancestor_id,

          (select count(1) from PERSON p2
           where p2.DEP_ID = tc.ancestor_id),
          count(p.NAME)

   from tc
   join PERSON p

       on tc.dep_id = p.DEP_ID
   group by tc.ancestor_id
   order by tc.ancestor_id

This query is courtsey of Lennart Jonsson who originally posted it to comp.databases.ibm-db2.

-- 
Erland Sommarskog, Stockholm, esquel_at_sommarskog.se

Received on Sun Aug 29 2010 - 06:52:09 CDT