select reucursivly

[Thorsten3]

I'd like to represent a hierarchy in a table like

id name parent1 aaa null
2 bbb 1
3 ccc 1
4 ddd 3

is it possible to select reucursivly?
For example for id 4 I would like a result like this:
aaa-ccc-ddd
For id 3 like this:
aaa-ccc

I read in the ansi SQL forum oracle can do it using START WITH and CONNECT BY syntax

Does anybody know a solution in DB2

thorsten

 

[blom0344]

Hello Thorsten,

your table Hierarchy:

id name parent
1 aaa null
2 bbb 1
3 ccc 1
4 ddd 3


WITH TEMPTABLE (parent,name) AS
(SELECT parent,name
FROM HIERARCHY
WHERE id = 4
UNION ALL
SELECT X.parent,X.name
FROM HIERARCHY X,
TEMPTABLE Y
WHERE X.parent = Y.parent)
SELECT name
FROM TEMPTABLE;

output is a bit different than just a string with dependents, comes as a column from the remp table....
(I did not test it either ......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[Thorsten3]

T. Blom,

I checked your select, but it didn't seem to work:

db2 => select * from test
ID NAME PARENT
----------- ---------- -----------
1 aaa -
2 bbb 1
3 ccc 1
4 ddd 3
4 record(s) selected.

WITH TEMPTABLE (parent,name) AS
(SELECT parent,name
FROM test
WHERE id = 4 UNION ALL
SELECT X.parent,X.name
FROM test X,
TEMPTABLE Y WHERE X.parent = Y.parent)
SELECT name FROM TEMPTABLE

gives me a never ending list of rows containing ddd

ddd
ddd
ddd
ddd
...

 

[blom0344]

made a mistake:

should be:

WITH TEMPTABLE (parent,name) AS
(SELECT parent,name
FROM test
WHERE id = 4 UNION ALL
SELECT X.parent,X.name
FROM test X,
TEMPTABLE Y WHERE Y.name = X.parent)
SELECT name FROM TEMPTABLE


(at least, I think I got it right this time, bit stressed cause it is the first day of the month, so lots of reporting to handle) T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Did the last example I posted do the trick? Kind of curious..... T. Blom
Information analyst
tbl@shimano-eu.com

 

[Thorsten3]

with your select I got an error message:

db2 => WITH TEMPTABLE (parent,name) AS (SELECT parent,name FROM test WHERE id = 4 UNION ALL SELECT X.parent,X.name FROM test X, TEMPTABLE Y WHERE Y.name = X.parent) SELECT name FROM TEMPTABLE

SQL0401N The data types of the operands for the operation "=" are not
compatible. SQLSTATE=42818

I played arround a little without exactly understanding this command at all, but this here seems to do what I want:

db2 => WITH TEMPTABLE (parent,id, name) AS (SELECT parent,id, name FROM test UNION ALL SELECT X.parent,X.id, Y.name FROM test X, TEMPTABLE Y WHERE Y.id = X.parent) SELECT name FROM TEMPTABLE WHERE id = 4

NAME
----------
SQL0347W The recursive common table expression "DB2INST1.TEMPTABLE" may
contain an infinite loop. SQLSTATE=01605

ddd
ccc
aaa

3 record(s) selected with 1 warning messages printed.

db2 =>