ORA-02287: sequence number not allowed here
if you try to reference a sequence's NEXTVAL pseudocolumn directly in the insert statement, like this:
INSERT INTO mytable ( entry_id, order_date, sum(quantity) ) MYSEQ.NEXTVAL, <-- INCORRECT SELECT TRUNC(SYSDATE), SUM(quantity) FROM orders WHERE GROUP BY order_date ORDER BY 2 ASC, 3 DESC;Solution is to create a trigger that will fire on INSERT statements, before a new row is inserted. Note that I am overriding the default correlation name NEW with NewID, by using the REFERENCING clause of the "CREATE TRIGGER" command:
CREATE OR REPLACE TRIGGER MYTRG BEFORE INSERT ON mytable REFERENCING NEW as NewID FOR EACH ROW BEGIN SELECT myseq.NEXTVAL INTO :NewID.ENTRY_ID FROM DUAL; END;Change your insert statement slightly:
INSERT INTO mytable ( order_date, sum(quantity) ) SELECT TRUNC(SYSDATE), SUM(quantity) FROM orders WHERE GROUP BY order_date ORDER BY 2 ASC, 3 DESC;
i tested it's working Fine.
ReplyDeleteThank you.