Re: INSERT nextval and UNION SELECT result?

From: Pierre Charpenay <pcharpenay_at_unilog.fr>
Date: Mon, 17 Jan 2000 12:43:42 +0100
Message-ID: <3883006E.7974A248@unilog.fr>

Phil R Lawrence a écrit :

> "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

Just try this :

INSERT into table

    ( dupcheckid, ... )
 select

       dupcheckid.nextval,
        field1,
        ...
 from (select one_field field1, ... from ...
          union
          select one_other field1,  .... from ...);

If you Oracle 7 or more, I think it would work (I don't try).

Pierre Received on Mon Jan 17 2000 - 05:43:42 CST