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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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