«

»

Nov 04

Recursive Query in Teradata

Guest Post 

  • Author – Saurabh Gupta


In this post we will go through recursive query with one example.

The recursive query features enables to query hierarchical data such as an organisation chart or bill of materials ( check the raw materials used to make a product ). Using Recursive query data with in the single table can be scanned multiple times using a single SQL statement.

A recursive query is useful when relationships between data elements  exist in multiple iterations. Recursive query consist of two parts a seeding query and a recursive query. Seeding query provides the initial records for which data needs to be iterated whereas Recursive query part helps in data iteration.

We will be using below syntax for recursive query :

Recursive query syntax :

 

WITH RECURSIVE < recursive_tablename >
(<columnname 1>,<columnname 2>,….<columnname n>)
AS
(
<SELECT * from seed_table>
UNION ALL
<select * from recursive_tablename join seed_table>
)
<SELECT * FROM recursive_tablename>;

 

1) Recursive_tablename is a derived recursive table defined using WITH RECURSIVE keyword.

2) Column names of recursive table is specified as <columname >. This column names will be finally displayed in the output.

3) Seeding query ( query which is used as the input for recursive logic ) is mentioned as the first select statement.

4) Second select statement is known as the recursive query and is joined with seed table for multiple iterations.

5) Third select statement finally gives the output of recursive logic.

Let’s consider a table,  Employee ( ddl shown below ) which stores –

 

CREATE VOLATILE TABLE  Employee
(
EMP_ID INTEGER,
MGR_ID INTEGER,
EMP_NAME VARCHAR(100)
)
ON COMMIT PRESERVE ROWS;

recursive1

Employee Id in column EMP_ID , Employee’s manager Id in column MGR_ID and Employee Name in column EMP_NAME e.g. Zoya is an employee with Employee Id 28006 and her direct Manager Id is 28005 .

One day Zoya wants to find out her first level, second level managers in the  hierarchy i.e.  all the direct and indirect manager above her in the hierarchy. Since employee table stores only first level of hierarchy (i.e. the direct manager which is employee id 28005 ( Patrick ) in case of Zoya), to find the hierarchy she needs to query database multiple times .This can be achieved very easily using a recursive query within a single sql statement.

 

WITH RECURSIVE MGR_JRS
(EMP_ID,
MGR_ID,
MGR_NAME,
DEPTH) AS
(
SELECT EMPL.EMP_ID, EMPL.MGR_ID, EMPL1.EMP_NAME AS MGR_NAME, 1 AS DEPTH
FROM Employee empl
inner join Employee empl1
on empl.mgr_id = empl1.emp_id
WHERE empl.EMP_ID=28006
UNION ALL
SELECT MGR_JRS.EMP_ID,
EMPL.MGR_ID,
empl1.EMP_NAME,
MGR_JRS.DEPTH + 1
FROM MGR_JRS INNER JOIN Employee empl
ON MGR_JRS.MGR_ID = EMPL.EMP_ID
inner join Employee empl1
on empl.mgr_id = empl1.emp_id
)
SELECT * FROM MGR_JRS;
 

Below points explains the above sql :

1) WITH RECURSIVE keyword specifies table MGR_JRS is a derived recursive table.

2) The first select statement :

 

               SELECT EMPL.EMP_ID, EMPL.MGR_ID, EMPL1.EMP_NAME AS MGR_NAME, 1 AS DEPTH
               FROM Employee empl
               inner join Employee empl1
               on empl.mgr_id = empl1.emp_id
               WHERE empl.EMP_ID=28006

recursive3
gives the Manager name and manager employee Id for Employee ID 28006 ( for Zoya ). So the first level manager for Zoya identified by above sql is Patrick. Data retrieved using above sql is internally stored in derived table MGR_JRS. This is often referred as seeding query.

3) Second select joins Employee table to MGR_JRS table which is getting populated as part of recursive logic. This is often referred as recursive query.

 

                SELECT MGR_JRS.EMP_ID,
                EMPL.MGR_ID,
                empl1.EMP_NAME,
                MGR_JRS.DEPTH + 1
               FROM MGR_JRS INNER JOIN Employee empl
               ON MGR_JRS.MGR_ID = EMPL.EMP_ID
               inner join Employee empl1
               on empl.mgr_id = empl1.emp_id

 Since MGR_JRS has one record with MGR_ID 28005 , it joins with employee table where EMP_ID is 28003 and in turn saves one more row in MGR_JRS.

recursive5

Now Zoya knows her second level manager is Lorena.

4) Each time a new row is added to MGR_JRS, the possibility exists for a new match and thus a higher level manager will be found in hierarchy.

5) It will recursively execute this join operation until it has found all managers for Zoya.

6) When the WITH table population is completed i.e. Step 5 is completed, the final SELECT will execute producing the below output.

recursive7

In this case Zoya has four levels of manager above her in the organization chart. Thus, a complex requirement of multiple iteration is very easily solved using recursive query.

Limitations :

Following are not allowed in recursive portion of query but allowed in seeding portion :

  • Subqueries and derived tables
  • Aggregation and OLAP functions
  • NOT IN and NOT EXISTS
  • GROUP BY and extended grouping functions
  • DISTINCT

TOP N function ,Cursors ,Stored procedures,Triggers are not permitted in recursive query

Summary :

 The recursive query feature enables multiple iterations against hierarchical data  in a single SQL statement.

If you like the post do share it on your social networking sites with the below share button 🙂

15 pings

Skip to comment form

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.