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

db2 function error! help

P: n/a
my function is
CREATE FUNCTION BOD_SEQ_NEXT
()
RETURNS NUMERIC(19)
LANGUAGE SQL
MODIFIES SQL DATA
FENCED
NOT DETERMINISTIC
EXTERNAL ACTION
BEGIN ATOMIC
declare v_seq_next NUMERIC(19) default -1;
-- If the sequence table was initialized at table creation with one row,
-- the delete & insert could be replaced with single update.
-- Also, the SEQ column need not be declared as PRIMAY KEY since
-- a sequence table should only contain one row.
-- delete from BOD_SEQ;
-- insert into BOD_SEQ (SEQ) values (default);
update TMGIIM.BOD_SEQ set SEQ = default;
select max(SEQ) into v_seq_next from TMGIIM.BOD_SEQ;
RETURN v_seq_next;
END@
but i get the 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:
SQL0104N An unexpected token "v_seq_next" was found following "select
max(SEQ) into". Expected tokens may include: "<space>". LINE NUMBER=18.
SQLSTATE=42601

(btw
db2 version is 7.2.1 and patch is FP12_WR21337
)
thanks
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On a microsocopic level there are two things wrong here that I see:
1. select max(SEQ) into v_seq_next from TMGIIM.BOD_SEQ;
needs to be:
SET v_seq_next = (select max(SEQ) from TMGIIM.BOD_SEQ);
2. MODOFIES SQL DATA is only allowed in TABLE function and only in
DB2 V8.1 FP4 and up for LUW.
DB2 UDB for z/OS supports MODIFIES SQL DATA is very specific contexts
(such as VALUES on INSERT or right-hand side of UPDATE SET.

On a macrosocopic level you seem to be using IDENTITY already. Why not
use a sequence instead.

Assuming you had to IDENTITY (for some odd reason) you can use
IDENTITY_VAL_LOCAL in V7 or SELECT FROM UPDATE in V8.1.4 to retrieve the
value instead of doing select max().

Cheers
Serge
Nov 12 '05 #2

P: n/a
i modify
set v_seq_next =(select max(SEQ) from TMGIIM.BOD_SEQ);

but the error message is

[IBM][CLI Driver][DB2/NT] SQL0628N Multiple or conflicting keywords
involving the "LANGUAGE SQL" clause are present SQLSTATE=42613

by the way
db2 version IS DB2/NT 8.1.6
Nov 12 '05 #3

P: n/a
That's because of the MODIFIES SQL DATA. It's not supported with
language SQL

Cheers
Serge
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.