Re: INSERT with subselect

In article <b32s78$4l8$1_at_rznews2.rrze.uni-erlangen.de>, atnite_at_freemail.ru says...
> 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.
>

You can't SELECT from a table and a sequence.nextval, and then use ORDER BY. Leave off the ORDER BY and it will work OK (You shouldn't be concerned with ordering data when you INSERT anyway).

• Tom