atnite_at_freemail.ru said...
> 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
>
For starters, you should never attempt to assign any kind of
"intelligence" to sequence numbers (such as an implied order). However,
if you insist, you'd have to do it with a procedural language - eg,
PL/SQL:
for x in (
select val
from some_table
where val between 10 and 20
order by val)
loop
insert into test values (1, my_seq.nextval, x.val);
end loop;
--
/Karsten
DBA > retired > DBA
Received on Thu Feb 20 2003 - 11:17:18 CST
