In Netezza I'm trying to use a sequence in a case statement but the sequence value doesn't increment

Question

Here is the sequence creation syntax used:

CREATE SEQUENCE BD_ID_SEQ AS INTEGER
    START WITH 999
    INCREMENT BY 1
    NO MINVALUE 
    NO MAXVALUE 
    NO CYCLE;

I have a table with the following values records:

b_id
-------
2547
NULL
2800
NULL
NULL
NULL
NULL

I run the following:

select case 
          when b_id is NULL then cast((select next value for bd_id_seq) as character varying(10)) 
          else b_id 
       end b_id
from table1;

The result comes to:

b_id
-------
2547
1000
2800
1000
1000
1000
1000

I was expecting:

2547
1000
2800
1001
1002
1003
1004

Any ideas why in the case statement the sequence doesn't seem to increment past the first value? Thanks, Ginni

Answer 1

you need to change the way you are calling the next value. Just remove the select and request the next value. Like below.

select case 
          when b_id is NULL then cast((next value for bd_id_seq) as character varying(10)) 
          else b_id 
       end b_id
from table1;