Re: SQL: how to retrieve records and make sure they arrive in RRN sequence

Joel

There's an RRN function. You should probably qualify the asterisk - not sure it's needed, but it always worked for me. So here's your statement.


create table qtemp/memotext as
(select mdcmdct.*, rrn(mdcmdct) as myrrn from mdcmdct
union
select mdcmtx1.*, rrn(mdcmtx1) as myrrn from mdcmtx1
) with data;


But I'm not sure this will help - here is from the documentation - SQL Programming -

"Any ORDER BY clause must appear after the last subselect that is part of the union."

So if you sort on MYRRN, things will be order by that, but there could be the same RRN in both files.

You could add a constant to each SELECT, like an 'A' or a 'B' - then sort on the constant first - give it an alias name, then the RRN - this'd guarantee RRN order for each - the rows would not be intermingled. I'm not sure THAT is what you want.

The RRN is an IBM i-only thing - I am curious about the need for this sorting, as you'd never use it in any other RDBMS.

Regards
Vern

On 2/12/2013 4:32 PM, Stone, Joel wrote:

Normally records seem to arrive in RRN sequence - if no other ORDER BY is stated.

However, when I use UNION to concat two SELECT results together, they are arriving in a random sequence.

How can I assure that the result set is in RRN seq?

However, I don't think I can include RRN in the SELECT because that field is NOT in the result table.

Thanks




______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________