I am trying to get absolute parent child mapping for below table, but my query keeps running into spool space error. please advise.
(SEL parent AS a, child AS d FROM p_ci_trust_t.parentof WHERE child='Mary'
SEL ancestor.a, p_ci_trust_t.parentof.child AS d
FROM ancestor JOIN p_ci_trust_t.parentof p
SEL a FROM ancestor WHERE d='mary'
This is not due to recursion :-)
Teradata was implemented before there was Standard SQL, the initial query language was called TEQUEL (TEradata QUEry Language), whose syntax didn't require to list tables within FROM (this was ported to SQL).
In your query you mixed TEQUEL and SQL when you aliased p_ci_trust_t.parentof as p. A table-alias is not really an alias, it replaces the tablename within that query. Using the original tablename doesn't result in an error message (as it does within most other DBMSes), but it causes a CROSS join: You got three tables (ancestor, p, p_ci_trust_t.parentof), but only one join-condition.
This is one of the reasons why you should run a quick explain :)
Simply replace p_ci_trust_t.parentof.child AS d with p.child AS d, when you still run out of spool you have circular data.