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.

Why not do it with a trigger?

Andy

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.

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