All Forums General
lbisht 1 post Joined 12/13
05 Oct 2014
recursive query spool space error

I am trying to get absolute parent child mapping for below table, but my query keeps running into spool space error. please advise.

ancestor(a,d) AS 
(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
ON ancestor.d=p.parent)
SEL a FROM ancestor WHERE d='mary'


parent  child
alice   carol
bob     carol
carol   dave
carol   geouge
dave    mary
eve     mary
mary    frank


dnoeth 4628 posts Joined 11/04
06 Oct 2014

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).

A simple RETRIEVE tablename.columnname; carried enough information for the Parser to resolve tablename and columnname.
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.


You must sign in to leave a comment.