January 18, 2017

Teradata recursive sql

Suppose we have a table:
create table accont_tbl
acct_id int,
manager_acct_id int,
name varchar(50)
)
which contains employee records with their supervisor.

Then the recursive sql is:
with recursive mgr_tbl(acct_id, manager_acct_id, mgr_name, depth) as
(
select
a.acct_id,
a.manager_acct_id,
a1.name as mgr_upn,
1 as depth
from account_tbl a
inner join account_tbl a1
on a.manager_acct_id = a1.acct_id
where a.acct_id = 8976
union all
select
mgr_tbl.acct_id,
a.manager_acct_id,
a1.name,
mgr_tbl.depth+1
from mgr_tbl
inner join account_tbl a
on mgr_tbl.manager_acct_id = a.acct_id
inner join account_tbl a1
on a.manager_acct_id = a1.acct_id
-- where mgr_tbl.mgr_upn <> 'email of root if root's manager is same as accout'
)
select * from mgr_tbl order by depth

No comments:

Post a Comment