Recursive queries in Teradata

Similarly to Oracle connect by level (see also http://van-maanen.com/index.php/2016/04/08/an-oracle-programme-with-a-loop/ ), Teradata has a structure whereby tables can be connect in order to create a potentially large table. Let us show some code:

with
RECURSIVE jaren(belastingjaar) as
(
   select belastingjaar
   from (select 2015 as belastingjaar)A
   union all
   select belastingjaar+1
   from jaren
   where belastingjaar < 2020
)
select belastingjaar from jaren;


This generates a series going from 2015 to 2020.

Or, in another example:

with
RECURSIVE trap(employee_id, naam) as
(
    select employee_id , naam
    from tom.organisatie
    where employee_id =1
    union
    all
    select organisatie.employee_id, organisatie.naam
    from tom.organisatie
    inner join trap
    on trap.employee_id = organisatie.manager_id
)
select employee_id,naam
from trap;

, which is based on this table

The purpose of the query is to provide an overview of all members who have have employee_id = 1 as their direct or indirect manager. From the table, we see that employee_id = 2,3 have employee_id=1 as their direct manager. However number 4,5 have number 2 as their direct manager and they are therefore indirectly managed by number 1.

The query provides the answer.


Door tom