Recursive query in teradata has three execution phases,1)Create an initial resultset 2)Repeated iteration 3)Termination query

Teradata: Recursive Query

Recursion is a process of self iterations until the required outcome is achieved. Recursion is typically consist of three steps:


Same way, a recursive query in teradata has three execution phases: Syntax: Recursive Query Structure
WITH RECURSIVE [any_recursive_tablename](col1,query_level)
AS
(
--Initialize the logic or Seed query
SELECT column_name1 AS col1,1 AS query_level FROM databasename.tablename

UNION ALL
--Recursive Join / Repeated iteration of the logic in the entire table

SELECT a.col1||b.col1 AS temp_col, query_level+1
FROM databasename.tablename a INNER JOIN [any_recursive_tablename] b
ON  a.col1 > b.col1

)
-- Termination Query
SELECT temp_col,query_level,character_length(temp_col) AS wordSize
FROM [any_recursive_tablename]
QUALIFY RANK() OVER (ORDER BY wordSize desc) = 1;


Example 1: Reverse a string using recursion.




Example 2: Concatenate a string from different rows




Example 3: Finding a parent recursively for all the nodes(rows).