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

netezza, sequence-sql

Solution

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;

Problem

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

Original source