Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: updating cols with sequence objects

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    SQL0348N is the error message corresponding to SQLCODE -348. That message says: SQL0348N
    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
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2011
    Posts
    4
    great thanks!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •