Recursive SQL PL ?
asceta
functions in DB2. A simple example:
CREATE OR REPLACE FUNCTION fibo (
k integer
)
RETURNS integer
BEGIN
IF k < 2 THEN
return 1;
ELSE
return fibo(k-1) + fibo(k-2);
END IF;
END !
ends up with rather unexpected error:
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL20481N The creation or revalidation of object xxxxxx
would result in an invalid direct or indirect self-reference. LINE
NUMBER=11.
SQLSTATE=429C3
I use DB2 9.7 Express
Anyone can help me solve that issue ?
Lennart
I don't think you can have the function call itself (I don't have
access to 9.7 so I haven't tried, but that seems to be what the error
message says). One option is to use a recursive cte in the function.
In your case:
create function fibo (k int)
returns bigint
return
with next (i, j, n) as (
values (bigint(0), bigint(1), k)
union all
select j, i+j, n-1 from next where n>1
)
select max(j) from next
@
[... ~]$ time db2 "values fibo(92)"
1
--------------------
7540113804746346429
1 record(s) selected.
real 0m0.046s
user 0m0.011s
sys 0m0.021s
There are restrictions on what kind of recursion you are allowed to in
a cte as well (I'm not entirely clear of what they are though). As an
example you can't do ackerman because it involves nested recursive
calls.
/Lennart
Serge Rielau
So do a dynamic 'SET ? = ..'
and then return the result of that.
Note that DB2 imposed a limit of 64 levels on nesting.
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
Lennart
I've got a private mail from a person that pointed out that in 9.7
there is a new concept, module. Since I don't have a 9.7 installation
yet, I haven't checked it out. From his description it looks as if you
can forward declare a function (pretty much the same way as you do
with a header file). Anyhow, here's his example:
CREATE MODULE M@
ALTER MODULE M PUBLISH FUNCTION FIBO ( K INTEGER ) RETURNS INTEGER@
ALTER MODULE M ADD FUNCTION FIBO ( K INTEGER )
RETURNS INTEGER
BEGIN
IF K < 2 THEN
RETURN 1;
ELSE
RETURN FIBO(K-1) + FIBO(K-2);
END IF;
END@
VALUES(M.FIBO(25))
1
-----------
121393
1 record(s) selected.
I'm curious, is it possible to define for example:
CREATE MODULE X@
ALTER MODULE X PUBLISH FUNCTION ACK ( i INT, j INT ) RETURNS INT@
ALTER MODULE X ADD FUNCTION ACK ( i INT, j INT )
RETURNS INT
BEGIN
IF i = 0 THEN
RETURN j+1;
ELSE IF j = 0 THEN
RETURN ACC(i-1, 1);
ELSE
RETURN ACC(i-1, ACC(i,j-1));
END IF;
END@
VALUES(X.ACC(3,6)) @
Anyone with a working 9.7 install that is willing to try?
/Lennart
Lennart
Sorry, make that:
CREATE MODULE X@
ALTER MODULE X PUBLISH FUNCTION ACK ( i INT, j INT ) RETURNS INT@
ALTER MODULE X ADD FUNCTION ACK ( i INT, j INT )
RETURNS INT
BEGIN
IF i = 0 THEN
RETURN j+1;
ELSE IF j = 0 THEN
RETURN ACK(i-1, 1);
ELSE
RETURN ACK(i-1, ACC(i,j-1));
END IF;
END@
VALUES(X.ACK(3,6)) @
/Lennart
HRY0009
>
> - Show quoted text -
Hello: I am trying to figure out how to get a recursive call working
with PL/SQL in DB2- a slightly different problem (can someone help?)
Anyhow, I tried the above in 9.7 There is still a mistake in the
code:
RETURN ACK(i-1, ACC(i,j-1));
should read:
RETURN ACK(i-1, ACK(i,j-1));
Anyhow, I still get the invalid direct or indirect self reference.
Can anyone offer any suggestions. The IBM page for this error is:
Thanks!
HRY0009
>
> Thanks!- Hide quoted text -
>
> - Show quoted text -
Hello:
I stand corrected (sometimes a lunch break does wonders.) The above
method works in 9.7.
Cheers!