By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,592 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,592 IT Pros & Developers. It's quick & easy.

user defined function

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a

"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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.