Re: INSERT with subselect

"Alex" <atnite_at_freemail.ru> wrote in message news:b32s78$4l8$1_at_rznews2.rrze.uni-erlangen.de...
> 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
>
>

I think there is a lot of overengineering going on here. Put the sequence number retrieval in a before insert trigger firing for each row.
Piece of cake.
But why on earth are you spawning sequence number from a java stored procedure?

Bye,
Flavio Received on Thu Feb 20 2003 - 14:41:44 CST