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.