Unanswered: Using sequence or identity in case statements
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
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 (
WHEN 'AAA' THEN NEXTVAL FOR SEQA
WHEN 'BBB' THEN NEXTVAL FOR SEQB
The other option would be to do this in a stored proc or a user defined function.
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.