Brilliant! Thank you, kind stranger :-)

4.spam@mail.ru wrote:
> 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