Recursive query in Teradata - Definition and Example using the WITH RECURSIVE keyword

Recursive Queries use the SEED query to iterate over the RECURSIVE block until the block is empty. 

Please go through the example below to understand the implementation.

/* Create a table to hold the data */

create multiset table financial.employee

(emp_id integer,

mgr_id integer,

ename varchar(50)

)primary index(emp_id);

insert into financial.employee(2000,,'Oliver');
insert into financial.employee(2001,2000,'tom');
insert into financial.employee(2002,2001,'santosh');
insert into financial.employee(2003,2001,'preeti');
insert into financial.employee(2004,2001,'sree');
insert into financial.employee(2005,2001,'tapas');
insert into financial.employee(2006,2001,'mani');
insert into financial.employee(2007,2002,'prabhu');

select * from financial.employee;

database financial;

As we see in the above data, each employee has a manager assigned to him/her. If the employee with id 2007 wants to query the table to know his managers (Level 1, Level 2 and so on), he should use a recursive query.

The RECURSIVE block (not in bold) will be executed and a row will be inserted in mgr_tbl( the recursive table). The seed is for employee 'Prabhu'. The iteration will continue till the time all the depth are not covered.

/* Let us now write a recursive query to find all the employee manager ladders */

/* The SEED part is marked in bold. The recursive part is executed till the time it returns rows */

WITH RECURSIVE mgr_tbl(emp_id, mgr_id, mgr_name, depth) AS

(

  SELECT a.emp_id, a.mgr_id, a1.ename as mgr_name, 1 as depth

  FROM  employee a

  inner join employee a1

  on a.mgr_id = a1.emp_id

  where a.emp_id = 2007

  UNION ALL

  SELECT mgr_tbl.emp_id, a.mgr_id, a1.ename

  ,mgr_tbl.depth+1

  FROM   mgr_tbl inner join employee a

  on mgr_tbl.mgr_id = a.emp_id

  inner join

  employee a1

  on a.mgr_id = a1.emp_id

  ---WHERE  a.mgr_id = b.emp_id

)

select * from mgr_tbl;

Result will be as follows:

 

The Depth represents Level of each Manager for employee id 2007 using recursive query

Please like us on Facebook or Google+ if you like our post. Leave your comments below.