CODEWITHSUNDEEP
db2sequence
Mike Stone
Mike Stone

Reputation: 44613

Increment a sequence twice in DB2

I need to get the next value of a sequence twice in DB2 (version 9.1). Rather than executing the following twice:

SELECT nextval FOR schema.sequence AS id
FROM dual

I would like to do something like:

SELECT nextval FOR schema.sequence AS id1,
       nextval FOR schema.sequence AS id2
FROM dual

Except the above only increments it once:

ID1         ID2        
----------- -----------
        643         643

  1 record(s) selected.

Am I forced to just query twice, or is there a way to increment it twice in 1 query?

Upvotes: 2

Views: 6644

Answers (4)

Josh
Josh

Reputation: 76

This is a a little complex, but it works (I verified by running it in DB2 v.9).

WITH GENERATED (KEYVAL) AS
(select 1
   from sysibm.sysdummy1
  UNION ALL
 SELECT KEYVAL + 1
   FROM GENERATED
  WHERE KEYVAL < 2)        -- how many you want

SELECT KEYVAL AS INCREMENT_NUMBER, NEXTVAL FOR schema.sequence
  FROM GENERATED;

This is some of my favorite code, and it is recursive SQL. You can do it without the sequence object, and it will just generate several rows of numbers. I use this sometimes to insert rows of generated test data.

Upvotes: 6

C. K. Young
C. K. Young

Reputation: 223003

Just because I'm picking on you Mike :-P, here's another solution:

VALUES NEXT VALUE FOR schema.sequence, NEXT VALUE FOR schema.sequence;

It will work if you do it as two rows, like here. But if you do it as one row:

VALUES (NEXT VALUE FOR schema.sequence, NEXT VALUE FOR schema.sequence);

it will return the same sequence value. :-(

Upvotes: 0

CHuck
CHuck

Reputation: 1

there is no sequence nor dual table in DB2

Upvotes: 0

Mike Stone
Mike Stone

Reputation: 44613

Ok, it's a hack, but the following works... I would love to see a cleaner answer though, so please post an answer if you know one!

SELECT nextval FOR schema.sequence AS id
FROM (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual) temp

Which results with:

ID         
-----------
        669
        670

  2 record(s) selected.

Upvotes: 0

Related Questions