Re: INSERT nextval and UNION SELECT result?

Change it to a nested query. I take no responsibility for any performance hits:

INSERT into table

    (
    dupcheckid,
    ...
    )
    select a.* from

       select
       dupcheckid.<*>nextval,
         field1,
         ...
     union
     select
         dupcheckid.currval,
         field1,
         ....) a

"Phil R Lawrence" <prlawrence_at_lehigh.edu> wrote in message news: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 Tue Jan 18 2000 - 23:57:44 CST