«

»

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 🙂

14 comments

Skip to comment form

  1. Foo Hoo

    Anybody have a recursive to get the database ownership hierarchy in DBC.Databases? I wrote one but is is a dog…
    Lock
    Row
    For
    Access
    WITH RECURSIVE DB
    (DatabaseName, OwnerName)
    AS
    (
    SELECT D.DatabaseName, D.OwnerName from DBC.Databases D
    UNION ALL
    select DB.DatabaseName, DB.OwnerName from DB join DBC.Databases D
    On
    DB.DatabaseName = D.OwnerName
    )
    SELECT * FROM DB;

  2. Sundar

    Why do delete is not working in recursive queries of Teradata ?

  3. kiran

    Hi admin you have described fastload as load as like that am looking for all utilities
    are bteq,multiload,tpump,tpt,fastexport. Please give a explanation as soon as possible we are waiting for explanations.

  4. surendra

    if any one have TD training videos please share the links.

  5. Admin

    Shelza,

    Your logic is in the right direction. But you need to have partition on first column EID.Also in Case statements it should be changed :

    Select
    x.EID,
    Max(Case when x.rn=1 then Address End) As Address1,
    Max(Case when x.rn=2 then Address End) As Address2,
    Max(Case when x.rn=3 then Address End) As Address3
    From
    (
    Select
    EID,
    Address,
    row_number() (over partition by EID order by Address asc) rn
    From
    ) x;

  6. Admin

    Thanks Guys, keep following the others posts on TeradataTech.

  7. Shelza

    Its answer would be:

    Select x.EID,
    case when x.rn=1 then address end as address1,
    case when x.rn=1 then address end as address2,
    case when x.rn=1 then address end as address3
    from
    (select row number() over (order by EID) as rn from table)x;

  8. Atchayaa

    Awwesome explanation

  9. Sridhar

    Wow very nice explanation with universable employee table.

  10. Srinivasareddy

    How to convert the vertical data into horizantal data
    EX:
    EID Address
    101 New york
    101 London
    101 JAPAN
    I want the data like this

    EID Addrss1 Address2 Address3
    101 New york London JAPAN

    1. satish

      Please let me know if you know the ans.

      1. admin

        select
        EID
        ,Max( case when Address = ‘New York’ then ‘New York’ end) as Address1
        ,Max( case when Address = ‘London’ then ‘London’ end) as Address2
        ,Max( case when Address = ‘JAPAN’ then ‘JAPAN’ end) as Address3
        from

        group by EID

        catch here to know all the values of address beforehand, in above query we assume that address column has only 3 distinct values as – New york, London, JAPAN

  11. vijay

    Clear understanding with simple example..

  12. Vivek

    Good example with Very nice explanation.

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>