SQL Recursive query
Budget: $10 – $30 USD
I am looking for help in one IBM DB2 query where I should get derive the third column(current_acct_id), and give the final value of this recursive action to to all the rows of 3 level, 2 level and 1 level:
ex:
old_acct_id new_acct_id current_acct_id
A B D
B C D
C D D
2nd level
X Y Z
Y Z Z
1st level
M N N
Actual :
old_acct_id new_acct_id current_acct_id
010203 654275 083763
654275 657426 083763
657426 083763 083763
similarly for 2 level :
076203 324275 457426
324275 457426 457426
similarly for 1 level:
324545 124427 457427
I have the query ready but I am getting few duplicates and not sure how to resolve them, can someone help :
Here is the query:
Select h.old_extr_acct_id,
h.new_extr_acct_id,
COALESCE(h3.new_extr_acct_id, h2.new_extr_acct_id, h.new_extr_acct_id) as current_acct_id,
h.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h.move_sales_history,
h.sales_hist_move_date,
h.hist_rltnp_key,
h.efct_dt
from advactp.hist_acct_rltnp h
inner join advactp.hist_acct_rltnp h2
on h2.old_extr_acct_id = h.new_extr_acct_id
inner join advactp.hist_acct_rltnp h3
on h3.old_extr_acct_id = h2.new_extr_acct_id
union
Select h5.old_extr_acct_id,
h5.new_extr_acct_id,
COALESCE(h6.new_extr_acct_id, h5.new_extr_acct_id, h4.new_extr_acct_id) as current_acct_id,
h5.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h5.move_sales_history,
h5.sales_hist_move_date,
h5.hist_rltnp_key,
h5.efct_dt
from advactp.hist_acct_rltnp h4
inner join advactp.hist_acct_rltnp h5
on h5.old_extr_acct_id = h4.new_extr_acct_id
inner join advactp.hist_acct_rltnp h6
on h6.old_extr_acct_id = h5.new_extr_acct_id
union
Select h9.old_extr_acct_id,
h9.new_extr_acct_id,
COALESCE(h9.new_extr_acct_id, h8.new_extr_acct_id, h7.new_extr_acct_id) as current_acct_id,
h9.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h9.move_sales_history,
h9.sales_hist_move_date,
h9.hist_rltnp_key,
h9.efct_dt
from advactp.hist_acct_rltnp h7
inner join advactp.hist_acct_rltnp h8
on h8.old_extr_acct_id = h7.new_extr_acct_id
inner join advactp.hist_acct_rltnp h9
on h9.old_extr_acct_id = h8.new_extr_acct_id
--2nd level
union
Select h7.old_extr_acct_id,
h7.new_extr_acct_id,
COALESCE(h8.new_extr_acct_id, h7.new_extr_acct_id) as current_acct_id,
h7.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h7.move_sales_history,
h7.sales_hist_move_date,
h7.hist_rltnp_key,
h7.efct_dt
from advactp.hist_acct_rltnp h7
inner join advactp.hist_acct_rltnp h8
on h8.old_extr_acct_id = h7.new_extr_acct_id
union
Select h8.old_extr_acct_id,
h8.new_extr_acct_id,
COALESCE(h8.new_extr_acct_id, h7.new_extr_acct_id)as current_acct_id,
h8.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h8.move_sales_history,
h8.sales_hist_move_date,
h8.hist_rltnp_key,
h8.efct_dt
from advactp.hist_acct_rltnp h7
inner join advactp.hist_acct_rltnp h8
on h8.old_extr_acct_id = h7.new_extr_acct_id
union
--1 level
Select h8.old_extr_acct_id,
h8.new_extr_acct_id,
h8.new_extr_acct_id,
h8.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h8.move_sales_history,
h8.sales_hist_move_date,
h8.hist_rltnp_key,
h8.efct_dt
from advactp.hist_acct_rltnp h8
where h8.new_extr_acct_id not in (
--filtering the third level
Select
h.new_extr_acct_id
from advactp.hist_acct_rltnp h
inner join advactp.hist_acct_rltnp h2
on h2.old_extr_acct_id = h.new_extr_acct_id
inner join advactp.hist_acct_rltnp h3
on h3.old_extr_acct_id = h2.new_extr_acct_id
union
Select
h5.new_extr_acct_id
from advactp.hist_acct_rltnp h4
inner join advactp.hist_acct_rltnp h5
on h5.old_extr_acct_id = h4.new_extr_acct_id
inner join advactp.hist_acct_rltnp h6
on h6.old_extr_acct_id = h5.new_extr_acct_
ex:
old_acct_id new_acct_id current_acct_id
A B D
B C D
C D D
2nd level
X Y Z
Y Z Z
1st level
M N N
Actual :
old_acct_id new_acct_id current_acct_id
010203 654275 083763
654275 657426 083763
657426 083763 083763
similarly for 2 level :
076203 324275 457426
324275 457426 457426
similarly for 1 level:
324545 124427 457427
I have the query ready but I am getting few duplicates and not sure how to resolve them, can someone help :
Here is the query:
Select h.old_extr_acct_id,
h.new_extr_acct_id,
COALESCE(h3.new_extr_acct_id, h2.new_extr_acct_id, h.new_extr_acct_id) as current_acct_id,
h.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h.move_sales_history,
h.sales_hist_move_date,
h.hist_rltnp_key,
h.efct_dt
from advactp.hist_acct_rltnp h
inner join advactp.hist_acct_rltnp h2
on h2.old_extr_acct_id = h.new_extr_acct_id
inner join advactp.hist_acct_rltnp h3
on h3.old_extr_acct_id = h2.new_extr_acct_id
union
Select h5.old_extr_acct_id,
h5.new_extr_acct_id,
COALESCE(h6.new_extr_acct_id, h5.new_extr_acct_id, h4.new_extr_acct_id) as current_acct_id,
h5.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h5.move_sales_history,
h5.sales_hist_move_date,
h5.hist_rltnp_key,
h5.efct_dt
from advactp.hist_acct_rltnp h4
inner join advactp.hist_acct_rltnp h5
on h5.old_extr_acct_id = h4.new_extr_acct_id
inner join advactp.hist_acct_rltnp h6
on h6.old_extr_acct_id = h5.new_extr_acct_id
union
Select h9.old_extr_acct_id,
h9.new_extr_acct_id,
COALESCE(h9.new_extr_acct_id, h8.new_extr_acct_id, h7.new_extr_acct_id) as current_acct_id,
h9.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h9.move_sales_history,
h9.sales_hist_move_date,
h9.hist_rltnp_key,
h9.efct_dt
from advactp.hist_acct_rltnp h7
inner join advactp.hist_acct_rltnp h8
on h8.old_extr_acct_id = h7.new_extr_acct_id
inner join advactp.hist_acct_rltnp h9
on h9.old_extr_acct_id = h8.new_extr_acct_id
--2nd level
union
Select h7.old_extr_acct_id,
h7.new_extr_acct_id,
COALESCE(h8.new_extr_acct_id, h7.new_extr_acct_id) as current_acct_id,
h7.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h7.move_sales_history,
h7.sales_hist_move_date,
h7.hist_rltnp_key,
h7.efct_dt
from advactp.hist_acct_rltnp h7
inner join advactp.hist_acct_rltnp h8
on h8.old_extr_acct_id = h7.new_extr_acct_id
union
Select h8.old_extr_acct_id,
h8.new_extr_acct_id,
COALESCE(h8.new_extr_acct_id, h7.new_extr_acct_id)as current_acct_id,
h8.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h8.move_sales_history,
h8.sales_hist_move_date,
h8.hist_rltnp_key,
h8.efct_dt
from advactp.hist_acct_rltnp h7
inner join advactp.hist_acct_rltnp h8
on h8.old_extr_acct_id = h7.new_extr_acct_id
union
--1 level
Select h8.old_extr_acct_id,
h8.new_extr_acct_id,
h8.new_extr_acct_id,
h8.hist_acct_rltnp_typ_id,
-- hd.hist_acct_rltnp_typ_nm,
h8.move_sales_history,
h8.sales_hist_move_date,
h8.hist_rltnp_key,
h8.efct_dt
from advactp.hist_acct_rltnp h8
where h8.new_extr_acct_id not in (
--filtering the third level
Select
h.new_extr_acct_id
from advactp.hist_acct_rltnp h
inner join advactp.hist_acct_rltnp h2
on h2.old_extr_acct_id = h.new_extr_acct_id
inner join advactp.hist_acct_rltnp h3
on h3.old_extr_acct_id = h2.new_extr_acct_id
union
Select
h5.new_extr_acct_id
from advactp.hist_acct_rltnp h4
inner join advactp.hist_acct_rltnp h5
on h5.old_extr_acct_id = h4.new_extr_acct_id
inner join advactp.hist_acct_rltnp h6
on h6.old_extr_acct_id = h5.new_extr_acct_