Next value function error

Question

I get the following error during postgresql execution: ERROR [HY000] ERROR: you can only use a 'next value(s)' function within a target list

What is wrong with this sql statement:

SELECT TRFCON.ID       
       ,   
       case when DDDCON.ID_CON = 0
       then
        NEXT VALUE FOR SEQ_DDD_CON   
        else
       DWHCON.ID_CON
       end ID_CON

  FROM TTT_CONSUMPTION TTTCON 

join DDDDWH_CON DWHCON on TTTCON.ID_ORG = DDDCON.ID_ORG
and TTTCON.ID_PRO = DDDCON.ID_PRO
and TTTCON.ID_REF = DDDCON.ID_REF

The DDL of the sequence is the following:

CREATE SEQUENCE SEQ_DDD_CON AS BIGINT
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE 
    NO MAXVALUE 
    NO CYCLE;

Your syntax and error message are MySQL's not Postgresql as suggested in the question tag — Clodoaldo Neto, Jul 24 '13 at 11:59
@ClodoaldoNeto I think he is trying to port it from MySQL to PG since MySQL does not have sequences. — DrColossos, Jul 24 '13 at 13:18
I'm using Netezza SQL and Netezza SQL is based on PostgreSQL — MasterX, Jul 24 '13 at 13:50

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

From the docs for CREATE SEQUENCE

Compatibility

CREATE SEQUENCE conforms to the SQL standard, with the following exceptions:

[...]

Obtaining the next value is done using the nextval() function instead of the standard's NEXT VALUE FOR expression.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 24 '13 at 09:26

DrColossos

12,656
3
46
67

I used nextval but the same error. case when DDDCON.ID_CON = 0 then nextval('SEQ_DDD_CON') else DWHCON.ID_CON end ID_CON – MasterX Jul 24 '13 at 09:39
Try `nextval('"SEQ_DDD_CON"')`. – DrColossos Jul 24 '13 at 09:44
case when DDDCON.ID_CON = 0 then nextval('"SEQ_DDD_CON"') else DWHCON.ID_CON end ID_CON -> same error: ERROR [HY000] ERROR: you can only use a 'next value(s)' function within a target list – MasterX Jul 24 '13 at 10:01
case when DDDCON.ID_CON = 0 then (select nextval(''SEQ_DDD_CON') else DWHCON.ID_CON end ID_CON is working!!! Thanks for your help – MasterX Jul 24 '13 at 13:11
SELECT TRFCON.ID , NEXT VALUE FOR SEQ_DDD_CON as ID_CON1 , (select nextval('SEQ_DDD_CON')) as ID_CON2 FROM TTT_CONSUMPTION TTTCON join DDDDWH_CON DWHCON on TTTCON.ID_ORG = DDDCON.ID_ORG and TTTCON.ID_PRO = DDDCON.ID_PRO and TTTCON.ID_REF = DDDCON.ID_REF Has anyone an idea why values for ID_CON1 and ID_CON2 are different values? It differs for 10000 after restarting the sequence. ID_CON1=20000 ID_CON2=10000 Kind regards MasterX – MasterX Jul 24 '13 at 13:17
The scalar subquery (SELECT NEXTVAL('SOME_SEQ')) solved my issue. As to the discrepancy in values, due to Netezza's parallel nature, you cannot guarantee consecutive sequence values as it distributes the query across blades and will cache blocks of values on a whim. – qSlug Jan 17 '15 at 08:35

Next value function error

1 Answers1

Linked