再帰with句02 Oracleのsys_connect_by_path関数を模倣

SQLパズル

IDTable
ID  OyaID
--  -----
 1   null
 2      1
 3      2
 4      3
 5      1
 6      5
 7      2
20   null
21     20
22     21

Oracleの下記のクエリと同じ結果を取得する。

select ID,OyaID,Level,sys_connect_by_path(to_char(ID),',') as Path
  from IDTable
start with OyaID is null
connect by prior ID = OyaID;

出力結果
ID  OyaID  Level  Path
--  -----  -----  ---------
 1   null      1  ,1
 2      1      2  ,1,2
 3      2      3  ,1,2,3
 4      3      4  ,1,2,3,4
 7      2      3  ,1,2,7
 5      1      2  ,1,5
 6      5      3  ,1,5,6
20   null      1  ,20
21     20      2  ,20,21
22     21      3  ,20,21,22

データ作成スクリプト

create table IDTable(
ID    integer not null primary key,
OyaID integer);

insert into IDTable values
( 1,null),
( 2,   1),
( 3,   2),
( 4,   3),
( 5,   1),
( 6,   5),
( 7,   2),
(20,null),
(21,  20),
(22,  21);
commit;

SQL

with X(ID,OyaID,Level,path) as(
select ID,OyaID,1,',' || cast(RTrim(char(ID)) as varchar(40))
  from IDTable
 where OyaID is null
union all
select b.ID,b.OyaID,Level+1,path || ',' || cast(RTrim(char(b.ID)) as varchar(40))
  from X,IDTable b
 where X.ID = b.OyaID)
select * from X;

解説

DB2で数値を文字にキャストする方法は、もっと簡単な方法がないですかねぇ

Port CONNECT BY to DB2(英語)

---