ORA-02287: sequence number not allowed here

Question

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);

Answer 1

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

Answer 2

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)
);