Solved

ORA-02287 sequence number not allowed here

Posted on 2011-02-23
6
1,318 Views
Last Modified: 2012-05-11
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
Comment
Question by:enrique_aeo
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964154
You can only select the nextval like that in 10g or above.

Are you on 10g?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34964171
you can't have sequences in a subquery
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964174
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Expert Comment

by:sdstuber
ID: 34964195
I also notice your select query only inserts 4 columns, but you have 5 columns specified in your insert
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34964218
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964278
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle 11g 23 107
Distinct values from all columns in a table?? PL SQL 4 51
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword 2 65
pivot rows to columns 1 36
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question