Solved

ORA-02287 sequence number not allowed here

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 36 82
Calculating percentages per course - Oracle Query 3 41
Encryption Decryption in Oracle 12 105
Oracle Syntax 8 56
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

911 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

23 Experts available now in Live!

Get 1:1 Help Now