CODEWITHSUNDEEP
oracle-database
fred9999999999
fred9999999999

Reputation: 309

ORA-02287: sequence number not allowed here

I am trying to select values from two tables and insert them into one table and calculate the number of placements in total per year. I keep getting an error saying sequence not allowed here

DROP table placement_cal CASCADE CONSTRAINTS;

CREATE TABLE placement_cal(
    cal_id  INTEGER NOT NULL,
    year    INTEGER,
    no_of_placements INTEGER,
    CONSTRAINT  pk_cal_dim PRIMARY KEY (cal_id)
);


INSERT INTO placement_cal (
SELECT cal_id.nextval  , EXTRACT(YEAR FROM start_date) , count(placement_id)
FROM placement
group by year);

INSERT INTO placement_cal (
SELECT cal_id.nextval  , EXTRACT(YEAR FROM start_date) , count(placement_id)
FROM placement_two
group by year);

Upvotes: 31

Views: 102846

Answers (5)

Rob
Rob

Reputation: 2472

In my case the ORDER BY clause was giving the error.

I had to do a CTE as so to get it to work.

INSERT INTO shipping_tracker (id, CREATION_DATE, PRIORITY_MONTH, comments, INSTITUTION_NO, INSTITUTION_NAME, email, is_normal)
WITH temp AS (
SELECT al.CREATION_DATE, al.PRIORITY_MONTH , al.COMMENTS , al.INSTITUTION_NO , al.INSTITUTION_NAME , al.EMAIL, 1
  FROM ACTIVATION_LOG al WHERE al.PRIORITY_MONTH NOT LIKE 'From%' ORDER BY CREATION_DATE
 ) SELECT shipping_tracker_seq.nextval, temp.CREATION_DATE, temp.PRIORITY_MONTH, temp.COMMENTS , temp.INSTITUTION_NO , temp.INSTITUTION_NAME , temp.EMAIL, 1 FROM temp;

Upvotes: 0

SANTOSH KUMAR
SANTOSH KUMAR

Reputation: 11

Whenever you are inserting data in one table taking the data from the other table, do not use order by clause to avoid the sequence number not allowed Error.

Upvotes: 1

Frederick
Frederick

Reputation: 872

Surprised I didn't see an actual solution here. Using a CTE is a work around. Something like this should work:

INSERT INTO placement_cal (
    WITH tempPlacement AS (
        SELECT EXTRACT(YEAR FROM start_date) year, count(placement_id) cnt
        FROM placement
        group by EXTRACT(YEAR FROM start_date)
    ) SELECT cal_id.nextval, year, cnt FROM tempPlacement
);

Upvotes: 7

Tony Andrews
Tony Andrews

Reputation: 132750

This query raises the exception:

SELECT cal_id.nextval  , EXTRACT(YEAR FROM start_date) , count(placement_id)
FROM placement
group by year;

This is because you cannot select a sequence value in a query with a group by clause.

Also, a group by clause must include all non-aggregate expressions from the select clause, which yours doesn't. I'm guessing that year is the alias for EXTRACT(YEAR FROM start_date), in which case this is the query you need:

INSERT INTO placement_cal
SELECT cal_id.nextval, year, cnt FROM
( SELECT EXTRACT(YEAR FROM start_date) year, count(placement_id) cnt
  FROM placement
  group by EXTRACT(YEAR FROM start_date)
);

Upvotes: 17

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

You can get the reason in FAQ

The following are the cases where you can't use a sequence:

For a SELECT Statement:

  • In a WHERE clause
  • In a GROUP BY or ORDER BY clause
  • In a DISTINCT clause
  • Along with a UNION or INTERSECT or MINUS
  • In a sub-query

Upvotes: 34

Related Questions