user defined function

[PDN]

Sorry for my bad english, I am french speaking
...
I have been tasked to write a UDF in order to return the maximum value
of an integer column in a given table and increment it by 1.
I have no experience of UDF so I have tried this :
CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
DECLARE SEQNO INTEGER;
SELECT MAX(ID) FROM CTIR006.TSTIDNTY INTO SEQNO
RETURN SEQNO
END
;
and I get the following SQL error

CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
DECLARE SEQNO INTEGER;
---------+---------+---------+---------+---------+---------+---------+------
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD DECLARE, TOKEN
ON
<INTEGER> WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 -1 367 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000'
X'FFFFFFFF'
X'0000016F' X'00000000' SQL DIAGNOSTIC INFORMATION

How should I write the body to correct this ? Could someone help me ?
Thanks in advance.
PDN

[Knut Stolze]

PDN wrote:

Sorry for my bad english, I am french speaking
..
I have been tasked to write a UDF in order to return the maximum value
of an integer column in a given table and increment it by 1.
I have no experience of UDF so I have tried this :
CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
DECLARE SEQNO INTEGER;
SELECT MAX(ID) FROM CTIR006.TSTIDNTY INTO SEQNO
RETURN SEQNO
END
;
and I get the following SQL error

CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
DECLARE SEQNO INTEGER;
---------+---------+---------+---------+---------+---------+---------+------ DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD DECLARE, TOKEN
ON
<INTEGER> WAS EXPECTED

I don't know exactly why you got this error (I assume you are on DB2 for zOS
or so?), but you should be able to rewrite the function body like this:

CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
RETURN SELECT MAX(ID) + 1
FROM FROM CTIR006.TSTIDNTY;

That might also give the DB2 optimizer a better chance to optimize the
statement where the function is used as opposed to the compound statement
you had.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Rhino]

"Knut Stolze" <st****@de.ibm.com> wrote in message
news:ch**********@fsuj29.rz.uni-jena.de...

PDN wrote:

Sorry for my bad english, I am french speaking
..
I have been tasked to write a UDF in order to return the maximum value
of an integer column in a given table and increment it by 1.
I have no experience of UDF so I have tried this :
CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
DECLARE SEQNO INTEGER;
SELECT MAX(ID) FROM CTIR006.TSTIDNTY INTO SEQNO
RETURN SEQNO
END
;
and I get the following SQL error

CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
BEGIN
DECLARE SEQNO INTEGER;
---------+---------+---------+---------+---------+---------+---------+----

--

DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD DECLARE, TOKEN
ON
<INTEGER> WAS EXPECTED

I don't know exactly why you got this error (I assume you are on DB2 for

zOS or so?), but you should be able to rewrite the function body like this:

CREATE FUNCTION S123456T ()
RETURNS INTEGER
NO EXTERNAL ACTION
LANGUAGE SQL
RETURN SELECT MAX(ID) + 1
FROM FROM CTIR006.TSTIDNTY;
I think Knut made a typo here: I think the preceding line will work better
with only one 'FROM' in it ;-)
That might also give the DB2 optimizer a better chance to optimize the
statement where the function is used as opposed to the compound statement
you had.

Rhino