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

    Unanswered: Using sequence or identity in case statements

    Hi:

    I am trying to use a sequence object in a case stmt and I get the following error.

    SELECT CASE EMPNO
    WHEN 000010 THEN SELECT NEXTVAL FOR AAASEQUENCE
    ELSE SELECT NEXTVAL FOR BBBSEQUENCE
    END

    DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: NEXTVAL;N 000010 THEN SELECT;CONCAT
    Message: An unexpected token "NEXTVAL" was found following "N 000010 THEN SELECT". Expected tokens may include: "CONCAT".

    What I would like to do eventually is to use a case stmt to auto generate values for the column using a sequence object base on the value of another column.

    I was wondering if something like this is possible.
    COLA VARCHAR(5) NOT NULL,
    COLB BIGINT NOT NULL GENERATED ALWAYS AS (
    CASE COLA
    WHEN 'AAA' THEN NEXTVAL FOR SEQA
    WHEN 'BBB' THEN NEXTVAL FOR SEQB
    END
    )

    The other option would be to do this in a stored proc or a user defined function.

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why not do it with a trigger?

    Andy

  3. #3
    Join Date
    Apr 2011
    Posts
    4
    Thanks was thinking of using a UDF - but I did not think of using a trigger, would there be any performance concerns with one vs the other.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Probably no difference in performance whether you do the work in a trigger or a UDF. There is one big advantage with using a trigger. You do not have to rely on the developer to use the logic to fill in the column properly, it will be done by the database every time.

    Andy

Posting Permissions

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