Solved

ORA-02287 sequence number not allowed here

Posted on 2011-02-23
6
1,290 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 76

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 73

Expert Comment

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

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 73

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 73

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 76

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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now