Unable to use a sequence object with UNION

Question

When I insert multiple rows in one sentence:

INSERT INTO ORG01.RC_REFERENCIA(O9030_IDEMPR,O9030_COD_INT,O9030_TIP_REF,O9030_ID_REFER,O9030_FEC_ALT,O9030_UIDALTA )  
SELECT '0015',(NEXT VALUE FOR OR4ADM.SQ_REFCRU),'NT','10001','2008-12-31 00:00:00.000','ab110699' FROM SYSIBM.SYSDUMMY1 UNION ALL  
SELECT '0015',(NEXT VALUE FOR OR4ADM.SQ_REFCRU),'NT','10002','20 08-12-31 00:00:00.000','ab017705' FROM SYSIBM.SYSDUMMY1

I get this error:

[INSERT - 0 row(s), 0.265 secs] [Error Code: -348, SQL State: 428F9] NEXT VALUE FOR OR4ADM.SQ_REFCRU CANNOT BE SPECIFIED IN THIS CONTEXT. SQLCODE=-348, SQLSTATE=428F9, DRIVER=4.18.60

When I try only with one select statement it works fine:

INSERT INTO ORG01.RC_REFERENCIA(O9030_IDEMPR,O9030_COD_INT,O9030_TIP_REF,O9030_ID_REFER,O9030_FEC_ALT,O9030_UIDALTA )  
SELECT '0015',(NEXT VALUE FOR OR4ADM.SQ_REFCRU),'NT','10001','2008-12-31 00:00:00.000','ab110699' FROM SYSIBM.SYSDUMMY1

.. 1 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.110/0.000 sec [1 successful, 0 warnings, 0 errors]

Any idea how can I resolve this error?

Stavr00 Stavr00 · Accepted Answer · 2016-06-02T13:34:28

This is a similar solution to Increment a sequence twice in DB2

Here is the INSERT from SELECT query, rewritten to avoid the limitation

INSERT INTO ORG01.RC_REFERENCIA(O9030_IDEMPR,O9030_COD_INT,O9030_TIP_REF,O9030_ID_REFER,O9030_FEC_ALT,O9030_UIDALTA )  
SELECT a,OR4ADM.SQ_REFCRU.NEXTVAL,b,c,d,e
FROM ( 
  SELECT '0015','NT','10001','2008-12-31 00:00:00.000','ab110699' FROM SYSIBM.SYSDUMMY1
  UNION ALL 
  SELECT '0015','NT','10002','2008-12-31 00:00:00.000','ab017705' FROM SYSIBM.SYSDUMMY1   
) AS t(a,b,c,d,e)

The secret is to create the data as a CTE first, then add the SEQUENCE object on the outermost SELECT.

Unable to use a sequence object with UNION

1 Answers