Hello everybody;


I need to do something special in db2 v9.5:
I have a table like this: (which is used by a messaging engine)

create table resource
(
id integer,
parent_id integer,
name char(10)
);

There is a foreign key between fields id and parent_id.

It contains data like this example:
id parentid name
-- --------- -------
1 9 abc
2 1 def
3 2 ghi
4 3 jkl
9 7 mno
19 17 pqr

I need to extract all historical parents and put them in a row, so not in columns.
If for example I would like to find all historical parents for id 4, then this should happen:
Look for parentid of 4: this returns 3
then look for parentid of 3: this returns 2
then look for parentid of 2: this returns 1
etc... until you no longer find a parent.

The result of this should come into a row like this:
ID parent1 parent2 parent3 parent4 parent5
4 3 2 1 9 7

With the following query I can get the historical parents:
WITH temp (id, parentid, name,n) AS
(SELECT a.* ,1 FROM resource a WHERE id = 4
UNION ALL SELECT new.*, n + 1 FROM temp old ,resource new WHERE old.parentid = new.id AND old.n < 5 AND
new.id = (SELECT xxx.parentid FROM resource xxx WHERE xxx.parentid = old.parentid) )
SELECT ID, PARENTID FROM temp

But this has 2 disadvantages:
1. You need to give the max number of iterations that the query wil run (here n < 5).
2. And it stores the parents in different records instead of all in 1 row:
ID PARENTID
----------- -----------
4 3
3 2
2 1
1 9
9 7

Furthermore, the query should be able to run not only in db2 9.5, but also in PostGres...

Any input is greatly appreciated.

Thank you very much in advance;
Carl

It is not necessary to specify the max number of iterations.
But, it may be better to specify limit of number of iterations to suppress SQL0347W message, like n < 1000(enough large number)

Concatenate parents in recursive common-table-expression.

Example 1: I don't know about PostGres.

Thank you very much Mr. Tonkuma.
I will try it out at work as soon as possible and I will keep you informed.
Once more: Thank you.

Yours sincerely;
Carl

Ps.: My father has known the founder of Sony Corporation personally.

You can use the following sql to find all id's parents....

WITH
resource(id , parentid , name) AS (
VALUES
( 1 , 9 , 'abc' )
, ( 2 , 1 , 'def' )
, ( 3 , 2 , 'ghi' )
, ( 4 , 3 , 'jkl' )
, ( 9 , 7 , 'mno' )
, ( 19 , 17 , 'pqr' )
)
, temp (id,parentid , parent_list , n) AS (
SELECT id,parentid
, VARCHAR( RTRIM( CHAR(parentid) ) , 50 )
, 1
FROM resource
--WHERE id = 4
UNION ALL
SELECT old.id,new.parentid
, parent_list || ', ' || RTRIM( CHAR(new.parentid) )
, n + 1
FROM temp old
, resource new
WHERE old.parentid = new.id
AND old.n < 1000
)
SELECT id, MAX(parent_list) as parent_list
FROM temp group by id
;

Hello people;

Now I have another issue:
They would like to have the result set reversed.

Example:
With the queries above we put all parents of a certain ID into 1 column.
Eg. for ID '4' you get a record like this:
ID 4
ParentIDs: 3, 2, 1, 9, 7

-> Actually the ParentIDs should be in reverse order now: 7, 9, 1, 2, 3

Is there any easy way to do this?

Thank you in advance.

Why did you asked the last question?
Couldn't you make the query from Example 1, by yourself?

Modify Example 1 to concatenate parerentids in reverse order.
More concrete, exchange "parent_list" and "RTRIM( CHAR(new.parentid) )" in Example 1,
and modify last fullselect to choose a row having max(n).

From "Example 1"