Recursive SQL PL ?
in [DB2]
|
Prev: strange query behaviour
Next: COUNT(*) Not accurate?
From: HRY0009 on 6 Apr 2010 13:59 On Apr 1, 1:03 pm, Lennart <erik.lennart.jons...(a)gmail.com> wrote: > On 31 mar, 20:17, asceta <marek.patr...(a)gmail.com> wrote: > > > > > > > I was wondering, whether it is possible to create recursive SQL PL > > 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 ? > > 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- Hide quoted text - > > - 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: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.messages.sql.doc/doc/msql20481n.html Thanks!
From: HRY0009 on 6 Apr 2010 16:02
On Apr 6, 11:59 am, HRY0009 <hry0...(a)yahoo.co.uk> wrote: > On Apr 1, 1:03 pm, Lennart <erik.lennart.jons...(a)gmail.com> wrote: > > > > > > > On 31 mar, 20:17, asceta <marek.patr...(a)gmail.com> wrote: > > > > I was wondering, whether it is possible to create recursive SQL PL > > > 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 ? > > > 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- Hide quoted text - > > > - 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: > > http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db... > > 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! |