Reputation: 309
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
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
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
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
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
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