updating cols with sequence objects

johnson55 · #1 (**permalink**) 04-26-11, 14:29

I have the following sql

UPDATE TABLEA
SET COL1 =
CASE COL2
WHEN 'A' THEN NEXTVAL FOR SEQUENCEA
WHEN 'B' THEN NEXTVAL FOR SEQUENCEB
END
WHERE COL3 = 1

I get the following error:

>[Error] Script lines: 1-10 -------------------------
DB2 SQL error: SQLCODE: -348, SQLSTATE: 428F9, SQLERRMC: NEXTVAL
Message: "NEXTVAL" cannot be specified in this context.

Do I need to put the next val in a variable before updating?

Thanks.

stolze · #2 (**permalink**) 04-26-11, 15:17

SQL0348N is the error message corresponding to SQLCODE -348. That message says: SQL0348N

Quote:

NEXT VALUE expressions cannot be specified in the following contexts:

* CASE expression
* ...

So you have to change your statement so that NEXTVAL is not used inside the case expression. You could try to wrap it into a SELECT (untested):

Code:

UPDATE ...
SET ... = ( SELECT NEXTVAL FOR sequencea FROM sysibm.sysdummy1 WHERE col2 = 'A' ) UNION ALL ( SELECT NEXTVAL FOR sequenceb FROM sysibm.sysdummy1 WHERE col2 = 'B' )
WHERE col3 = 1

johnson55 · #3 (**permalink**) 04-26-11, 16:12

great thanks!!

Internet Services

Web Development

Digital Marketing

Consumer Tech