Re: Sort Order in Nested query

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Thu, 31 Jan 2013 19:42:17 +0000
Message-ID: <CAOuMUT5zpV2Pm6Vh1bdDs84qrNDZn4bjtacY12e6sDuom-gJng_at_mail.gmail.com>

Hi Niall,
I'm taking transactions from a staging table and loading them into another table. I'm using the sequence to generate surrogate key values for the Target table.

The problem is I need to batch the transactions into buckets based on certain criteria and the sum of the amounts in a bucket can't exceed a certain value. I resorted to a plsql loop to do this.

The logic requires that the records in the cursor are sorted and that's where I ran into the issue with sorting and sequences.

I could leave the sequence out of the query and increment the sequence for each iteration of the loop but I wasn't mad about that idea as I think (could be wrong) that that would involve a context switch each time the sequence was incremented.

That make any sense?

Thanks,
Fergal
On 31 Jan 2013 19:11, "Niall Litchfield" <niall.litchfield_at_gmail.com> wrote:

> I'm confused as to the purpose of a sequence.nextval in the select. The
> query will return different results each time it is run surely. You could
> probably store a hi gh value in a 1 row table and use the connect by prior
> trick, but it seems like we're missing important details.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 31 2013 - 20:42:17 CET