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:

• Initialize the logic or Seed query
• Repeated iteration of the logic in the entire table
• Termination Condition

• Create an initial resultset
• Repeated iteration based on the existing resultset
• Termination query to return the required resultset

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;

• Sample Table: Creating a sample volatile table rec_example with 2 columns for testing.

  
  CREATE MULTISET VOLATILE TABLE rec_example
  (
   id INTEGER,
   name VARCHAR(50)
  )
  PRIMARY INDEX(id)
  ON COMMIT PRESERVE ROWS;
  

  
  
• Data Population: Inserting sample data for reversing string.

  
  INSERT INTO rec_example VALUES(1, 'Pradeep Khatri');
  INSERT INTO rec_example VALUES(2, 'Teradata Database');
  

  
  
• Recursive Query: Query to reverse string.

  
  WITH
  RECURSIVE RecReverseStringTable(id, name, name_len, qry_level, reverse_string,tm)
  AS(   
  --Initialize the logic or Seed query
        SELECT id, 
               name, 
               CHARACTER_LENGTH(name), 
               1,  --Starting Query level from 1
               CAST(''  AS VARCHAR(50)),
               current_timestamp(6)
          FROM rec_example
  UNION ALL
  --Recursive Join / Repeated iteration of the logic in the entire table
       SELECT
            id,
            name,
            name_len,
            qry_level+1,
            SUBSTRING(name FROM qry_level FOR 1) || reverse_string,
            tm 
        FROM RecReverseStringTable 
        WHERE qry_level <= name_len
  )
  -- Termination Query
  SELECT id,
          name,
          reverse_string
      FROM RecReverseStringTable 
      WHERE name_len+1 = qry_level ORDER BY 1;
     
     
  Output:
      id       name                           reverse_string
  ----------  ------------------------------  ------------------------------
           1  Pradeep Khatri                  irtahK peedarP
           2  Teradata Database               esabataD atadareT
  

  Note: Starting TD15, Teradata provides direct function to reverse a string, visit Teradata String Functions to learn more about string functions.

• Sample Table: Creating a sample volatile table rec_example with 2 columns for testing.

  
  CREATE MULTISET VOLATILE TABLE rec_example
   (
    id INTEGER,
    code CHAR(1)
   )
   PRIMARY INDEX(id)
  ON COMMIT PRESERVE ROWS;
  

  
  
• Data Population: Inserting sample data for Concatenating string from different rows.

  
  INSERT INTO rec_example(1,'A');
  INSERT INTO rec_example(2,'B');
  INSERT INTO rec_example(3,'C');
  INSERT INTO rec_example(4,'D');
  

  
  
• Recursive Query: Query to Concatenate string.

  
  WITH RECURSIVE RecConcatenateString(code,qry_level)
  AS
  (
  --Initialize the logic or Seed query
  SELECT  MIN(CAST(code AS VARCHAR(10))) AS code,
          1   --Starting Query level from 1
  FROM rec_example
  UNION ALL
  --Recursive Join / Repeated iteration of the logic in the entire table
  SELECT trim(a.code) || ',' || trim( b.code),
         qry_level+1
  FROM rec_example a 
    INNER JOIN RecConcatenateString b
  ON  a.code > b.code
  )
  -- Termination Query
  SELECT code,
         qry_level,
         CHARACTER_LENGTH(code) concat_string_len
   FROM RecConcatenateString
   QUALIFY RANK() OVER (ORDER BY  concat_string_len desc) = 1;
  
  
  Output:   
  code         qry_level   concat_string_len
  ----------  ----------  -------------------
  D,C,B,A              4                    7
  

  Note: Teradata also provides other function to achieve similar result, visit Teradata Misc Functions to learn more about it.

• Sample Table: Creating a sample volatile table rec_example with 2 columns for testing.

  
  CREATE VOLATILE TABLE Searchlist_tbl(
      search_Id INTEGER,
      value_txt VARCHAR(50),
      parent_search_id     INTEGER
  )
  PRIMARY INDEX(search_Id)
  ON COMMIT PRESERVE ROWS;
  

  
  
• Data Population: Inserting sample data for finding a parent recursively for all the nodes(rows).

  
  INSERT INTO Searchlist_tbl VALUES(1,'search',null);
  INSERT INTO Searchlist_tbl VALUES(2,'search1',1);
  INSERT INTO Searchlist_tbl VALUES(3,'search2',2);
  INSERT INTO Searchlist_tbl VALUES(4,'search3',3);
  INSERT INTO Searchlist_tbl VALUES(5,'search4',4);
  INSERT INTO Searchlist_tbl VALUES(6,'search5',5);
  
  INSERT INTO Searchlist_tbl VALUES(7,'search3',null);
  INSERT INTO Searchlist_tbl VALUES(8,'search4',7);
  INSERT INTO Searchlist_tbl VALUES(9,'search5',8);
  

  
  
• Recursive Query: Query to finding a parent recursively for all the nodes(rows).

  
  WITH RECURSIVE RecursiveParent(search_Id,parent_search_Id,LVL)
  AS
  (
  --Seed Query
  SELECT  search_Id ,search_id as parent_search_Id,1
  FROM Searchlist_tbl WHERE parent_search_id IS NULL
  
  UNION ALL
  --RECURSIVE Join
  
  SELECT a.search_Id  ,b.parent_search_id, LVL+1
  FROM Searchlist_tbl a INNER JOIN RecursiveParent b
  ON  a.parent_search_Id = b.search_Id 
  )
  SELECT search_Id,parent_search_Id,case when search_Id=parent_search_Id THEN null ELSE parent_search_Id END orig_parent,lvl
  FROM RecursiveParent
  order by 1;
  
  
  Output: 
   search_Id  parent_search_Id  orig_parent   LVL
  ----------  ----------------  -----------  ----
           1                 1            ?     1
           2                 1            1     2
           3                 1            1     3
           4                 1            1     4
           5                 1            1     5
           6                 1            1     6
  
           7                 7            ?     1
           8                 7            7     2
           9                 7            7     3