|
The following query will recursively traverse the parent-child relationships and generate a hierarchical keyword by catenating
the IDs as it descends into the structure. Two copies of the result are then unioned, one to serve as an L marker and the
other as an R marker. These keywords are designed so that they collate correctly in a depth first left to right order. We
use the dense rank function to enumerate the result before pivoting left/right node pairs to generate the desired
output. The code shown below runs in parallel DB2 UDB v.8 or higher.
The assumed table structure...
C:\usr\Articles>db2 describe table org
Column
Type
name
name Length Scale Nulls
------------------------------
------------------ -------- ----- ------
ID
INTEGER 4
0 Yes
NAME
CHARACTER 20
0 Yes
TYPE
CHARACTER 3
0 Yes
PID INTEGER
4 0 Yes
4 record(s) selected.
The query...
-- SQL Query to compute L/R values
-- Assume table ORG (ID, PID, LVL, ....)
--
Top node is characterized by PID is NULL, all other nodes have a PID value
-- Assume that no more than 20 levels need to
be traversed.
-- The following query will generate the L/R numbers...
with
HNODE as (
select
ID as
ID,
PID as PID
from ORG
),
NODE (ID, PID, LVL, HKEY) as (
select H.ID, H.PID, 0,
varchar(rtrim(char(H.ID)),1000)
from HNODE H where PID is null
union all
select H.ID, H.PID, T.LVL - 1 ,
varchar(T.HKEY || '|' || rtrim(char(H.ID)))
from HNODE H , NODE T
where H.PID=T.ID and T.LVL > -20
),
LRNODE (TYPE, ID, PID, LVL, LRKEY)
as (
select 'L' as TYPE,H.ID, H.PID,
H.LVL, H.HKEY
from NODE H
union all
select 'R' as TYPE,H.ID, H.PID, H.LVL, H.HKEY || 'R'
from NODE H
),
LRNODE1 as (
select TYPE,ID,PID,LVL,
dense_rank() over (order by LRKEY) as SEQ
from
LRNODE
),
LRNODE2 as (
select ID,PID,LVL,
max(case when TYPE = 'L' then SEQ END)
as L,
max(case when TYPE = 'R' then SEQ END) as R
from LRNODE1
group
by ID,PID,LVL
)
select * from LRNODE2
order by L
fetch first 50 rows only
;
The data...
Sample ORG table contents
Query results
|
