Wednesday, July 6, 2016

A workaround for ORA-02287: sequence number not allowed here when using GROUP BY in a subquery

When you use GROUP BY in the subquery of an INSERT statement, Oracle will throw

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;

1 comment: