Re: transitive closure

-CELKO- wrote:
> >> I yet to see a case that is handled by recursive "with" and not handled by "connect by". <<
>
> Want to try an Ackeramnn function?
>
> A(x,y) = if x = 0
> then (y+1)
> else if y = 0
> then A(x-1, 1)
> else A(x-1, A(x,y-1));

Dont think it is possible, at least for DB2's version of "with". It is not allowed to do nested recursive calls.

I'm not familiar with connect by so bear with me. Is it possible to handle multiple branches? Silly example below

/Lennart

db2 -t "CREATE FUNCTION TOWERS (n int, from int, to int, help int)

    RETURNS table(from int, to int, move_nr int)     LANGUAGE SQL CONTAINS SQL
    NO EXTERNAL ACTION DETERMINISTIC
    RETURN
WITH trace (x, f, t, h, c) AS (

    values (n, from, to, help, power(2, n-1))     union all
    select x-1, f, h, t, c + power(2, x-2) from trace where x>1     union all
    select x-1, h, t, f, c - power(2, x-2) from trace where x>1 ) SELECT f, t, c FROM trace";

db2 "select from, to from table(towers(3,1,2,3)) x order by move_nr"

FROM TO
----------- -----------
SQL0347W The recursive common table expression "LELLE.TRACE" may contain an
infinite loop. SQLSTATE=01605

          1           2
          3           2
          3           1
          1           2
          2           3
          1           3 
          1           2