enrique_aeo
asked on
ORA-02287 sequence number not allowed here
i have this sequence
-- Create sequence
create sequence SEQ_CODIGOAPLICACIONFONDOT MP
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_CODIGOAPLICACIONFONDOT MP.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_CODIGOAPLICACIONFONDOT MP.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_CODIGOAPLICACIONFONDOT MP.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
-- Create sequence
create sequence SEQ_CODIGOAPLICACIONFONDOT
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_CODIGOAPLICACIONFONDOT
from TMP_SOLICITUD
where AplicacionPorcentaje1 is not null or AplicacionCodProducto1 is not null or AplicacionCredito1 is not null
union
select SEQ_CODIGOAPLICACIONFONDOT
from TMP_SOLICITUD
where AplicacionPorcentaje2 is not null or AplicacionCodProducto2 is not null or AplicacionCredito2 is not null
select SEQ_CODIGOAPLICACIONFONDOT
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Forget my post. To be honest I cannot remember what version of Oracle allows the use of NEXVAL on an insert into select statement.
Are you on 10g?