INSERT nextval and UNION SELECT result?

From: Phil R Lawrence <prlawrence_at_lehigh.edu>
Date: Fri, 14 Jan 2000 16:20:34 -0500
Message-ID: <85o3v2$gno@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 - 15:20:34 CST