Re: Keeping ROWNUM across two SELECT statements

Further testing shows that the part of the SQL that was causing me problems I actually excluded from the post. The actual SQL looked more like:

CREATE SEQUENCE my_Sequence;

INSERT INTO Results_Table
SELECT my_Sequence.NEXTVAL, Val1, Val2 FROM Table1 ORDER BY Val2;

And this is what gave me that "ORA-02287: sequence number not allowed here" error.

Changing the statement to:

INSERT INTO Results_Table
(SELECT my_Sequence.NEXTVAL, Val1, Val2 FROM Table1
ORDER BY Val2);

Gives the "ORA-00907: missing right parenthesis" error.

I was finally able to get this work by doing.

INSERT INTO Results_Table
SELECT my_Sequence.NEXTVAL, ST.* FROM
(SELECT Val1, Val2 FROM Table1

ORDER BY Val2) ST; Received on Fri Sep 09 2005 - 13:32:58 CDT