Recursive Queries in Teradata

Recursive queries are used when a parent child relationship exists within the table and you may want to cycle through all the way up to the parent

In Teradata, You can specify a recursive query by:

• Preceding a query with the WITH RECURSIVE clause
  
• Creating a view using the RECURSIVE clause in a CREATE VIEW statement
  

Table Recursive Syntax:

   WITH RECURSIVE temp_table (employee_number) AS
   ( SELECT root.employee_number
     FROM employee root
     WHERE root.manager_employee_number = 801
   UNION ALL
     SELECT indirect.employee_number
     FROM temp_table direct, employee indirect
     WHERE direct.employee_number = indirect.manager_employee_number
   )
   SELECT * FROM temp_table ORDER BY employee_number;

Recursive view syntax:

   CREATE RECURSIVE VIEW hierarchy_801 (employee_number) AS
   ( SELECT root.employee_number
     FROM employee root
     WHERE root.manager_employee_number = 801
   UNION ALL
     SELECT indirect.employee_number
     FROM hierarchy_801 direct, employee indirect
     WHERE direct.employee_number = indirect.manager_employee_number
   );

Recursive queries with depth control:

To avoid infinite looping, we can specify a optional depth parameter

   WITH RECURSIVE temp_table (employee_number, depth) AS
   ( SELECT root.employee_number, 0 AS depth
     FROM employee root
     WHERE root.manager_employee_number = 801
   UNION ALL
     SELECT indirect.employee_number, direct.depth+1 AS newdepth
     FROM temp_table direct, employee indirect
     WHERE direct.employee_number = indirect.manager_employee_number
     AND newdepth <= 5
   )
   SELECT * FROM temp_table ORDER BY employee_number;

nVector

posted on 12 May 18