Finally

November 18, 2008


This is an answer to Troels Arvin comment to my earlier post comparing different hierarchical methods.

version

output

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

Advertisements

5 Responses to “Finally”

  1. Troels Arvin Says:

    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?

  2. Charles Says:

    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.

  3. j kraai Says:

    the limit can be moved to another WITH select

    here’s a working DB2 version:

    with
    intLim as ( select * from ( values 17 ) t(n) )
    ,ints(num) as (
    select * from ( values 1 ) t(n)
    union all
    select num+1 from ints
    where num < (select * from intLim)
    )
    select * from ints where num >= 5;

    • j kraai Says:

      arithmetic series:

      with
        intPrm as ( select * from ( values (-3,19,3) ) prm(min,max,inc) )
      , integers(n) as (
        select * from (select min from intPrm) t(n)
        union all
        select n+(select inc from intPrm) from integers
        where  n+(select inc from intPrm) <= (select max from intPrm)
      )
      select n from integers;
      

      yields:

      N
      -----------
      SQL0347W  The recursive common table expression "WWWTRN.INTEGERS" may contain
      an infinite loop.  SQLSTATE=01605
      
               -3
                0
                3
                6
                9
               12
               15
               18
      
        8 record(s) selected with 1 warning messages printed.
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: