在Netezza中，我尝试在case语句中使用序列，但是序列值不会增加 [英] In Netezza I'm trying to use a sequence in a case statement but the sequence value doesn't increment

问题描述

以下是所使用的序列创建语法:

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

我运行以下命令:

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;

结果是:

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

我期待中:

2547
1000
2800
1001
1002
1003
1004

有什么想法为什么在case语句中序列似乎没有超过第一个值? 谢谢， 金妮

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

推荐答案

，您需要更改调用下一个值的方式.只需删除select并请求下一个值即可.如下所示.

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;

这篇关于在Netezza中，我尝试在case语句中使用序列，但是序列值不会增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！