Re: INSERT nextval and UNION SELECT result?

Try an inline view method.
Presumably you want the UNION to
eliminate duplicates before the sequence is used, so you could express it like this (hacking your original code a little)

>INSERT into table
> (
> dupcheckid,
> ...
> )
> select
> dupcheckid.nextval,

         vxx.*
      from  (

            select


>        field1,


>        ...

    ) vxx

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Phil R Lawrence wrote in message <85o3v2$gno_at_fidoii.CC.Lehigh.EDU>...
>
>"Phil R Lawrence" <prlawrence_at_lehigh.edu> wrote in message
>news:85o08c$nb8_at_fidoii.CC.Lehigh.EDU...
>> I want to insert the nextval along with the results of a SELECT into a
>> table. Can't seem to get the syntax right. Oracle books don't cover
this
>> one in the examples. I've tried:
>...
>> INSERT into table
>> (
>> dupcheckid,
>> ...
>> )
>> select
>> dupcheckid.<*>nextval,
>> field1,
>> ...
>
>OK the problem here is that my SELECT is actually a UNION SELECT. If I
chop
>off the UNION and second half, it works ok when written as above. However,
>it tanks with the UNION:
>____________________
>INSERT into table
> (
> dupcheckid,
> ...
> )
> select
> dupcheckid.<*>nextval,
> field1,
> ...
> union
> select
> dupcheckid.currval,
> field1,
> ....
>____________________
>ORA-02287: sequence number not allowed here (DBD: error possibly near <*>
> indicator
>
>
>So, anyone know how to insert a nextval and the results of a UNION SELECT
in
>one INSERT statement?
>
>Thanks,
>Phil R Lawrence
>
>
>
Received on Fri Jan 14 2000 - 16:49:39 CST