Hello.
Try this:
--------------
with a(ID_A, ID_B) as
(
values
(1800, 1804),
(1800, 1808),
(1806, 1809),
(1808, 1810),
(1808, 1812),
(1809, 1815),
(1810, 1811)
), t(level, id_a, id_b, chain) as
(
select distinct 1, id_a, id_a, cast(digits(id_a) as varchar(50))
from a
where not exists
(
select 1
from a a2
where a2.id_b=a.id_a
)
UNION ALL
select t.level+1, t.id_a, a.id_b, t.chain||digits(a.id_b)
from a, t
where a.id_a=t.id_b
)
select dense_rank() over(order by id_a) as grp,
substr(
repeat(' ', level-1)||
char(case level when 1 then id_a else id_b end)
,1,20)
--, level, chain
from t
order by id_a, chain;
--------------

> Friends:
>
> In a DB2 UDB LUW table, I have a table with pairs of equivalent ID's.
> What I want to do is assign all equivalent IDs to the same group
> number, including those that are transitively related, i.e., if A = B
> and B = C then A = C, so I'd group all three together.
>
> Although they're not related in a composition fashion per se, it seems
> like the way to go conceptually is to consider the ID relationships as
> a reporting tree (ID_A would be the manager and ID_B would be the
> employee) and assign all IDs that share the same root to the same
> group.
>
> For instance, let's say I have the following pairs
>
> ID_A ID_B
> ---- ----
> 1800 1804
> 1800 1808
> 1806 1809
> 1808 1810
> 1808 1812
> 1809 1815
> 1810 1811
>
> I'd have two trees (sideways):
>
> 1800 1804
> 1808
> 1810
> 1811
> 1812
> and
>
> 1806
> 1809
> 1815
>
> I'm struggling with the following:
>
> 1. How to group based on a shared *root* (I'd hate to have to build a
> chain column, e.g., for 1811: "1800-->1808-->1810" and do something
> like DENSE_RANK() OVER(ORDER BY SUBSTR(CHAIN,1,4)--that seems
> unreliable, and the ID is not always the same length)
>
> 2. How to write a recursive CTE that accomodates multiple, independent,
> trees.
>
> What I'd like to end up with is this:
>
> ID GRP
> ---- ---
> 1800 1
> 1804 1
> 1808 1
> 1810 1
> 1811 1
> 1812 1
> 1806 2
> 1809 2
> 1815 2
>
> I feel like I'm close--I've read Serge's "CONNECT BY" article and
> Molinaro's chapter "Hierarchical Queries" in his _SQL Cookbook_, but
> I'm just not able to stitch it all together.
>
> Would anyone care to lend a hand?
>
> Thanks,
>
> --Jeff