oracle ORA-02287: 此处不允许使用序列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34546009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-02287: sequence number not allowed here
提问by fred9999999999
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);
回答by Rahul Tripathi
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
以下是不能使用序列的情况：
对于 SELECT 语句：
- 在 WHERE 子句中
- 在 GROUP BY 或 ORDER BY 子句中
- 在 DISTINCT 子句中
- 连同 UNION 或 INTERSECT 或 MINUS
- 在子查询中
回答by Tony Andrews
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 byclause.
这是因为您不能在带有group by子句的查询中选择序列值。
Also, a group byclause must include all non-aggregate expressions from the selectclause, which yours doesn't. I'm guessing that yearis the alias for EXTRACT(YEAR FROM start_date), in which case this is the query you need:
此外，group by子句必须包含该子句中的所有非聚合表达式select，而您的则没有。我猜这year是 的别名EXTRACT(YEAR FROM start_date)，在这种情况下，这是您需要的查询：
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)
);
回答by Junaid Shirwani
Do it like this You can use the sequence like this, For the group by part i recommend you follow the other answer from @Tony INSERT INTO placement_cal ( cal_id.nextval , EXTRACT(YEAR FROM start_date) , count(placement_id) FROM placement group by year);
这样做您可以使用这样的顺序，对于按部分分组，我建议您按照@Tony INSERT INTOplacement_cal ( cal_id.nextval , EXTRACT(YEAR FROM start_date) , count(placement_id) FROMplacement group by年）;
INSERT INTO placement_cal (
cal_id.nextval , EXTRACT(YEAR FROM start_date) , count(placement_id)
FROM placement_two
group by year);
