INSERT with subselect

Hi there,
I have the following problem:
- suppose I have a table

CREATE TABLE test (id number, seq number, val number);

I try to fill this table by an INSERT with subquery like this:

CREATE SEQUENCE my_seq;
INSERT INTO test(ID, seq, val) SELECT 1, my_seq.nextval, val FROM some_table_with_data WHERE (val BETWEEN 10 and 20) ORDER BY val ASC;

It fails since ORACLE gives an error saying that seqences are not allowed here. Ok, I wrote Java stored procedure that generates a sequence numbers for me so this is now working. However, it looks like subquery first selects data from some_table_with_data and associates it with next generated value and only after this sorts the newly generated data according to given ORDER BY clause.
So, I wonder if there is any way to control how the subquery fills the data in the destination table. I must have sequence numbers generated accordingly to the sort order.

Thanks,

Alex Received on Thu Feb 20 2003 - 09:28:41 CST