Finally
November 18, 2008
This is an answer to Troels Arvin comment to my earlier post comparing different hierarchical methods.
The predicate limiting the size of the Integers relation would fit more naturally to the outer query block:
with integers(num) as (
select 1 num from dual
union all
select num+1 from integers
) select * from integers where num < 10 ;
Pushing predicate inside recursive query definition doesn’t work, however (same as in competitor products).
November 18, 2008 at 9:15 pm
That’s interesting. It the following interpretation correct?:
Oracle currently doesn’t support SQL:1999 recursive (WITH RECURSIVE), but it’s in a beta-version of an Oracle 11 update?
November 18, 2008 at 10:31 pm
Exactly.
September 25, 2009 at 8:53 pm
See error text from ORA-32043, 32044, 32045 for more insight into factored sub-query recursion.
32043 “recursive WITH clause needs an initialization branch”
// *Cause: A WITH clause query referred to itself (recursive) but did not
// have a branch in a UNION ALL with no references to itself (the
// initialization branch or anchor member).
// *Action: Rewrite the recursive WITH query to have a branch in a UNION
// ALL operation with no references to itself.
32044 “cycle detected while executing recursive WITH query”
// *Cause: A recursive WITH clause query produced a cycle and was stopped
// in order to avoid an infinite loop.
// *Action: Rewrite the recursive WITH query to stop the recursion or use
// the CYCLE clause.
32045 “maximum level of recursion reached while executing recursive WITH query”
// *Cause: A recursive WITH query reached the maximum level of recursion
// specified and was stopped.
// *Action: Increase the maximum level of recursion or use the CYCLE clause.