Teradata Recursive SQL to find Parents of Parent of given Child table

Question

I have installed the Teradata Express and loaded publicly available database for teradata called 'Finance' in it.

It has some parent-child relationships using PK-FK.

My requirement is to find out all the parents,grant parents of the given child.

I have made use of DBC.All_RI_Parents metadata table and written a recursive SQL to find out the same but it is not giving the parents of parent and great parents of given child.

WITH RECURSIVE CTE
(indexid,
indexname,
parentdb,
parenttable,
parentkeycolumn,
childdb,
childtable,childkeycolumn,
DEPTH) AS
(
select indexid,indexname,parentdb,parenttable,parentkeycolumn,
childdb,childtable,childkeycolumn,1 AS DEPTH 
from DBC.All_RI_Parents where childdb='finance' and childtable='fin_loan'
UNION ALL
SELECT par.indexid,par.indexname,par.parentdb,par.parenttable,par.parentkeycolumn,
par.childdb,par.childtable,par.childkeycolumn ,
CTE.DEPTH + 1
FROM CTE INNER JOIN DBC.All_RI_Parents par
ON CTE.parentkeycolumn = par.childkeycolumn
and CTE.parenttable = par.childtable
and CTE.parentdb = par.childdb
)
SELECT * FROM CTE 

I have downloaded the dataset from

https://community.teradata.com/t5/Blog/Demo-databases-for-download/ba-p/85684

Please let me know if I am doing wrong here.

Answer 1

I figured it out by looking deeply into data that I should not add CTE.parentkeycolumn = par.childkeycolumn as column name can be different. After removing this,It is working perfectly,

WITH RECURSIVE CTE
(indexid,
indexname,
parentdb,
parenttable,
parentkeycolumn,
childdb,
childtable,childkeycolumn,
DEPTH) AS
(
select indexid,indexname,parentdb,parenttable,parentkeycolumn,
childdb,childtable,childkeycolumn,1 AS DEPTH 
from DBC.All_RI_Parents where childdb='finance' and childtable='fin_loan'
UNION ALL
SELECT par.indexid,par.indexname,par.parentdb,par.parenttable,par.parentkeycolumn,
par.childdb,par.childtable,par.childkeycolumn ,
CTE.DEPTH + 1
FROM CTE INNER JOIN DBC.All_RI_Parents par
ON 
CTE.parenttable = par.childtable
and CTE.parentdb = par.childdb
)
SELECT * FROM CTE