?
Solved

NEXTVAL Failing on INSERT INTO

Posted on 2005-03-09
5
Medium Priority
?
2,061 Views
Last Modified: 2012-08-14
Hi,

I am trying to populate a table from the result of joining records from different tables and adding a unique record indicator.  I have used the following statement:

INSERT INTO EXIST_EQUIP (REC_ID, E_CODE, W_CODE, FACILITY, UNIT, COMPONENT_TAG,
                         ASSOCIATE_TAG, COMPONENT_NAME, GEN_ARG, TIME_STAMP)
SELECT DISTINCT SEQ_EXIST_EQUIP.NEXTVAL, C.E_CODE, A.OWNER_CODE, B.FACILITY, B.UNIT, B.COMPONENT_TAG,
       B.ASSOCIATE_TAG, B.COMPONENT_NAME, B.GEN_ARG, B.TIME_STAMP
FROM EQUIPMENT_DATA_FILE C, TIDECPMS A, TIDWOTCL B
WHERE C.facility = B.FACILITY
AND C.UNIT = B.UNIT
AND TRIM(C.component_tag) = TRIM(B.component_tag)
AND TRIM(C.ASSOCIATE_TAG) = TRIM(B.associate_tag)
AND A.model_wo_number = B.work_order_nbr
ORDER BY E_CODE

The error I get is ORA-02287: sequence number not allowed here.  What am I doing wrong?  The problem probably isn't particularly difficult, but it is extremely urgent.
0
Comment
Question by:tlchavet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13495701
Its probably the combination of DISTINCT and the sequence, AFAIK this is not allowed.
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13495721
yup, its SEQUENCE and DISTINCT:

  1* select distinct view_name, alarm_id_seq.nextval from user_views
SQL> /
select distinct view_name, alarm_id_seq.nextval from user_views
                                        *
ERROR at line 1:
ORA-02287: sequence number not allowed here


0
 
LVL 11

Accepted Solution

by:
cjjclifford earned 2000 total points
ID: 13495747
if you need the DISTINCT, do the DISTINCT call in a sub-query, and use the NEXTVAL with the results of the subquery...

e.g.

SELECT seq_exist_equip.nextval, e_code, owner_code, facility, unit, component_tag, associate_tag, component_name, gen_arg, time_stamp
from
(
SELECT DISTINCT C.E_CODE, A.OWNER_CODE, B.FACILITY, B.UNIT, B.COMPONENT_TAG,
       B.ASSOCIATE_TAG, B.COMPONENT_NAME, B.GEN_ARG, B.TIME_STAMP
FROM EQUIPMENT_DATA_FILE C, TIDECPMS A, TIDWOTCL B
WHERE C.facility = B.FACILITY
AND C.UNIT = B.UNIT
AND TRIM(C.component_tag) = TRIM(B.component_tag)
AND TRIM(C.ASSOCIATE_TAG) = TRIM(B.associate_tag)
AND A.model_wo_number = B.work_order_nbr
)

I.e. I wrapped your query (minus the sequence) as a sub query...

Note that the order by is not necessary for an INSERT INTO .. SELECT ... statement...
0
 
LVL 1

Author Comment

by:tlchavet
ID: 13495820
It turns out it wasn't the distinct causing the problem - it was the order by.  I had already tried taking it out.  However, removing the order by solved the problem.  Thanks!
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 13495876
Glad to be of help...

btw, its actually both ORDER and DISTINCT that were causing problems :-) Learning every day!!!

Cheers,
C.


0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

765 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