Re: Hierarchies within Database

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Sat, 04 Aug 2001 21:14:49 -0400
Message-ID: <3B6C9E09.8434AA49_at_ca.ibm.com>

Hi Anthony,

Praise to google!.... I was affraid I have to redo that again, here is a solution I posted to a similar problem: "How to remember the path in a recursion"

To get a decent sorting you'll need to have upper limits for the number of siblings on a given level and fill with leading zeros (denser encodings can be thought of e.g. 0...Z for a base of 36 instead of 10 per digit): 00001.00010. and so on. You are limited in DB2 by a total of 32K rowsize for the size of the VARCHAR holding the path.

FYI, I'll write a technote for future reference, no desire to hunt that one down again :-)

Note that SQL Server 2000 also supports table functions (they call them parameterized views). I don't know about SQL Servers support for recursion.

Oracle supports CONNECT BY for recursion. I don't think they have table functions.

Informix has a "bladelette" that does this kind of recursive numbering. It's quite neat, but I forgot how it works.

---

Hi,

I got asked via email how to dump a hierarchical relationship to the screen using
a string to indicate the path with the position. Just thought the solution might be usefull for more than just that case, so here is a general example that can be customized. The script uses SQL table functions of DB2 UDB V71 to encaspulate the recursion.
RANK() is used to dump the positions..
It can be rewritten into a simple recursive query for DB2 V6.1.

Cheers
Serge

create table hier(id char(8), parent char(8)); insert into hier values
('1', NULL),
('2', NULL),
('11', '1'),
('12', '1'),
('111', '11'),
('112', '11'),
('113', '11'),
('121', '12'),
('122', '12'),
('1211', '121'),
('21', '2'),
('22', '2'),
('211', '21'),
('212', '21'),
('213', '21'),
('221', '22'),
('222', '22'),
('2211', '221');

create function get_direct_childs(code varchar(30), parent char(8)) returns table(code varchar(30), id char(8)) reads sql data deterministic no external action return
select code || '.' || rtrim(char(rank() over (order by child_id))), child_id
  from (select id from hier

        where hier.parent
              = get_direct_childs.parent)
       as T(child_id);

create function get_rec_childs(root char(8)) returns table(code varchar(30), id char(8)) reads sql data deterministic no external action return
with rec(code, id)
  as (values(cast('1' as varchar(30)), root)

      union all
      select t.code, t.id
        from rec, table(get_direct_childs(rec.code, rec.id)) as T)

  select code, id from rec;

db2 => select * from table(get_rec_childs(char('1'))) as t order by 1;

CODE                           ID
------------------------------ --------

SQL0347W The recursive common table expression "SRIELAU.REC" may contain an
infinite loop. SQLSTATE=01605

1                              1
1.1                            11
1.1.1                          111
1.1.2                          112
1.1.3                          113
1.2                            12
1.2.1                          121
1.2.1.1                        1211
1.2.2                          122

  9 record(s) selected with 1 warning messages printed. Received on Sun Aug 05 2001 - 03:14:49 CEST