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 :

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 –

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.

Below points explains the above sql :

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

2) The first select statement :

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.

 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 🙂