ORA-02287: serial 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 per year. I keep getting the message that the sequence is 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 the FAQ

The following are cases where you cannot use a sequence:

For a SELECT statement:

• In the WHERE clause
• In a GROUP BY or ORDER BY clause
• In the DISTINCT clause
• Along with UNION or INTERSECT or MINUS
• In subquery

Answer 2

This request throws an 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.

In addition, the group by clause should include all non-aggregated expressions from the select clause, which you don’t have. I assume that year is an alias of 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) ); 

Answer 3

Do it as follows. You can use a sequence like this for a group in parts, I recommend that you follow another answer from @Tony INSERT INTO place_cal (cal_id.nextval, EXTRACT (YEAR FROM start_date), count (place_id) FROM of the placement group by year);

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