Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1408
  • Last Modified:

ORA-02287 sequence number not allowed here

i have this sequence
-- Create sequence
create sequence SEQ_CODIGOAPLICACIONFONDOTMP
minvalue 1
maxvalue 99999999999999999999999999
start with 1
increment by 1
nocache;

i try execute this query
Insert into TEMP_APLICACIONFONDO (CODIGOAPLICACIONFONDO, NUMEROINSTRUCCION, PORCENTAJE, CODIGOPRODUCTO, NUMEROCREDITO )
      select * from
            (
            select SEQ_CODIGOAPLICACIONFONDOTMP.NEXTVAL, AplicacionPorcentaje1 as ApliPor, AplicacionCodProducto1 as ApliCodProd, AplicacionCredito1 as AplicaCred
              from TMP_SOLICITUD
             where AplicacionPorcentaje1 is not null or AplicacionCodProducto1 is not null or AplicacionCredito1 is not null
            union
            select SEQ_CODIGOAPLICACIONFONDOTMP.NEXTVAL, AplicacionPorcentaje2 as ApliPor, AplicacionCodProducto2 as ApliCodProd, AplicacionCredito2 as AplicaCred  
              from TMP_SOLICITUD
             where AplicacionPorcentaje2 is not null or AplicacionCodProducto2 is not null or AplicacionCredito2 is not null
            select SEQ_CODIGOAPLICACIONFONDOTMP.NEXTVAL, AplicacionPorcentaje5 as ApliPor, AplicacionCodProducto5 as ApliCodProd, AplicacionCredito5 as AplicaCred  
              from TMP_SOLICITUD
             where AplicacionPorcentaje5 is not null or AplicacionCodProducto5 is not null or AplicacionCredito5 is not null
            )  s
            
            but i have this error:
            ORA-02287 sequence number not allowed here
0
enrique_aeo
Asked:
enrique_aeo
  • 3
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
You can only select the nextval like that in 10g or above.

Are you on 10g?
0
 
sdstuberCommented:
you can't have sequences in a subquery
0
 
slightwv (䄆 Netminder) Commented:
From a previous question of yours you are using 9.2.0.1.

You will need to select the nextval into a variable and insert that variable.

If you are doing this insert statement outside code of code where you can create and use a variable, you will need to write a before insert trigger to populate CODIGOAPLICACIONFONDO.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberCommented:
I also notice your select query only inserts 4 columns, but you have 5 columns specified in your insert
0
 
sdstuberCommented:
change your select to something like this...

and then also make sure you are inserting the right number of columns
either remove a column from the insert or select an additional column



SELECT seq_codigoaplicacionfondotmp.NEXTVAL, aplipor, aplicodprod, aplicacred
  FROM (SELECT aplicacionporcentaje1 AS aplipor,
               aplicacioncodproducto1 AS aplicodprod,
               aplicacioncredito1 AS aplicacred
          FROM tmp_solicitud
         WHERE    aplicacionporcentaje1 IS NOT NULL
               OR aplicacioncodproducto1 IS NOT NULL
               OR aplicacioncredito1 IS NOT NULL
        UNION
        SELECT aplicacionporcentaje2 AS aplipor,
               aplicacioncodproducto2 AS aplicodprod,
               aplicacioncredito2 AS aplicacred
          FROM tmp_solicitud
         WHERE    aplicacionporcentaje2 IS NOT NULL
               OR aplicacioncodproducto2 IS NOT NULL
               OR aplicacioncredito2 IS NOT NULL
        UNION
        SELECT aplicacionporcentaje5 AS aplipor,
               aplicacioncodproducto5 AS aplicodprod,
               aplicacioncredito5 AS aplicacred
          FROM tmp_solicitud
         WHERE    aplicacionporcentaje5 IS NOT NULL
               OR aplicacioncodproducto5 IS NOT NULL
               OR aplicacioncredito5 IS NOT NULL) s
0
 
slightwv (䄆 Netminder) Commented:
sdstuber is correct with the subselect.  

Forget my post.  To be honest I cannot remember what version of Oracle allows the use of NEXVAL on an insert into select statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now