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;

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

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.

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.

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 🙂