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
______________________________________________________________________
