Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You can only select the nextval like that in 10g or above.

Are you on 10g?
you can't have sequences in a subquery
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.
I also notice your select query only inserts 4 columns, but you have 5 columns specified in your insert
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.