|
View previous topic :: View next topic
|
| Author |
Message |
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 131
Location: brisbane
|
|
|
|
for the following table:
P C
1 2
2 3
2 4
4 5
I need to generate the set of all paths (Parent to Child) starting at (say) 1. this would yield :
result (in one column only)
"1-2"
"1-2-3"
"1-2-4"
"1-2-4-5"
I can do this in oracle, but can't see a way in Db2 (z/OS) because of restrictions on back-referencing anchor rows. |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Please explain, how you got "1-2-4-5"? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
first row 1 ==> 2
third row 2 ==> 4
last row 4 ==> 5 |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Thanks Enrico.
| Quote: |
| but can't see a way in Db2 (z/OS) because of restrictions on back-referencing anchor rows. |
Did you think or tried any sql using OLAP- RANK function or Lag or Lead to compare next vs previous row(s)? |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Got what you're trying.
Recursive queries only work with
| Quote: |
| Common Table Expressions |
Sorry, I don't have one sample here with me.
But maybe tomorrow I can show you one... |
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 83
Location: Lower Saxony (DE)
|
|
|
|
Hi and good morning,
entered your sample data in my experimental table "SELF_REF_TEST" which has the columns
MY_ID INTEGER NOT NULLable
PARENT_ID INTEGER NULLable
TEXT VARCHAR(255) NOT NULLable
and some more that are not relevant in this context
Try the following query - where »WITH struktur« describes the common table expression.
| Code: |
WITH struktur
( gen_level
, id_path
, start_id
, my_id
, parent_id
, text )
AS ( SELECT 0
, VARCHAR(DIGITS(my_id), 999)
, my_id AS start_id
, my_id
, parent_id
, text
FROM self_ref_test
-- WHERE parent_id IS NULL -- Alle Strukur-Listen für alle Root-Elemente (ohne Parent)
-- WHERE parent_id = 0 -- Alle Strukur-Listen für alle eigentl. Root-Elemente (ohne Parent)
WHERE my_id = 59083 -- Strukur-Listen nach unten ab diesem Element
UNION ALL
SELECT str.gen_level + 1
, VARCHAR( str.id_path
||DIGITS(srt.my_id)
, 999)
, str.start_id
, srt.my_id
, srt.parent_id
, srt.text
FROM self_ref_test
srt
INNER JOIN struktur str
ON srt.parent_id = str.my_id
)
SELECT gen_level
, my_id
, parent_id
, start_id
, id_path
, text
FROM struktur
ORDER BY id_path
-- ORDER BY gen_level
-- , parent_id
-- , my_id
WITH UR
; |
You can see the second part of the union clause in it references to itself.
The VARCHAR(DIGITS(...)) clause is necessary to convert binary integer values as CHAR to concat 'em.
GEN_LEVEL indicates the hierarchy level
START_ID means the ID of the root.
ID_PATH represents something similar to you desired result, but with my IDs.
The result is
| Code: |
GEN_LEVEL MY_ID PARENT_ID START_ID ID_PATH TEXT
--------- ----- --------- -------- ---------------------------------------- -------
0 59083 {} 59083 0000059083 1-One
1 59084 59083 59083 00000590830000059084 2-Two
2 59085 59084 59083 000005908300000590840000059085 3-Three
2 59086 59084 59083 000005908300000590840000059086 4-Four
3 59087 59086 59083 0000059083000005908400000590860000059087 5-Five |
Hope this is helpful for you |
|
| Back to top |
|
|
|
|
