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

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

I am trying to UNION two files - the live MEMO file and the history MEMO file.

I want the result to have the same format as the MEMO file.

So if I use your example, then the RRN will come into the format as a new field at the end of the record, and programs will get a level check because the fields no longer match.

I am sorting on MEMO#, but I need to add RRN to ensure that the result is the same as in the PFs.

Thanks



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Tuesday, February 12, 2013 4:59 PM
To: Midrange Systems Technical Discussion
Subject: 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
> ______________________________________________________________________