Mike Nolan wrote:
> I have the following insert to populate a new table:
>
> insert into pending_tnmt_sec
> select tseceventid, tsecsecno,
> nextval('sec_seq'),
> tsecrtddt
> from tnmtsec
> order by tsecrtddt,tseceventid,tsecsecno;
>
> I need to access this data in a particular order which may change over
> time but the initial order I want is in the order by clause.
>
> The problem is, I'm not getting the data into the right order based
> on the sequence values being inserted:
In your example, I would expect the nextval() to be called during the
"fetch", before the ordering. You could probably do something like:
INSERT INTO pending_tnmt_sec
SELECT foo.*, nextval('sec_seq') FROM
(
SELECT tseceventid, ...
ORDER BY tsecrtddt,tseceventid,tsecsecno
) AS foo
;
I'm not sure whether the SQL standard requires the ORDER BY to be
processed in the sub-select. From a relational viewpoint, I suppose you
could argue that ordering is strictly an output feature.
--
Richard Huxton
Archonet Ltd
