12 May 2018        Add to Favorites   Report

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;



Read great educational content like this and a lot more ! Create my free account now 🎁

Copied