5

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

mustaccio
  • 18,234
  • 16
  • 48
  • 57
user3254441
  • 51
  • 1
  • 2

1 Answers1

6

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;
Niederee
  • 4,155
  • 25
  • 38