× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Joel

I'm going to say NO. The ORDER BY is over the entire result set, which has records from both files. RRNs will likely be duplicated.

Question - do you want the records from each all to be together? Or do you want them mixed but in RRN order?

Let's say you have these in File1:

RRN SOMEFIELD
1 bill
2 fred
3 george

And these in File2:

RRN SOMEFIELD
1 mary
2 sally
3 abigail

Do you want the first option, each file together in RRN order? Like this?

1 bill
2 fred
3 george
1 mary
2 sally
3 abigail

Or sorted only by RRN? Like this?

1 bill
1 mary
2 sally
2 fred
3 george
3 abigail

A problem with the latter is, you can't guarantee the order, that is, which file's record comes first. Unless you have some other sorting column, as I suggested - a constant, given an alias.

But if you already HAVE a table with the extra columns, you can select all the others and still ORDER BY columns NOT in the field list.

I was beginning to think you need a double-clutch here - was not sure it's possible in a single statement. The reason is, you mentioned level checks due to adding columns.

But then I thought of using a correlated table expression, no ORDER BY needed, then the actual SELECT in your CREATE TABLE. The CTE is in effect the first step of 2, if you want to think of it that way.

I experimented with QIWS/QCUSTCDT. Try the following in STRSQL -

WITH TMP AS (SELECT CUSNUM, LSTNAM, RRN(QCUSTCDT) MYRRN FROM QIWS/QCUSTCDT)
SELECT CUSNUM, LSTNAM FROM TMP ORDER BY MYRRN

Here are the results =

CUSNUM LSTNAM
938,472 Henning
839,283 Jones
392,859 Vine
938,485 Johnson
397,267 Tyron
389,572 Stevens
846,283 Alison
475,938 Doe
693,829 Thomas
593,029 Williams
192,837 Lee
583,990 Abraham

Now try the table expression -

SELECT CUSNUM, LSTNAM, RRN(QCUSTCDT) MYRRN FROM QIWS/QCUSTCDT

Those results are here -

CUSNUM LSTNAM MYRRN
938,472 Henning 1
839,283 Jones 2
392,859 Vine 3
938,485 Johnson 4
397,267 Tyron 5
389,572 Stevens 6
846,283 Alison 7
475,938 Doe 8
693,829 Thomas 9
593,029 Williams 10
192,837 Lee 11
583,990 Abraham 12

Note that the first 2 columns are in the same order.

This looks as if it could work! And it could produce either option I presented above.

HTH!
Vern

On 2/13/2013 9:20 AM, Stone, Joel wrote:
My objective is to keep the records in the same sequence that they exist in the file - ie ARRIVAL SEQ. Will ROW_NUMBER() get me there??



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tom E Stieger
Sent: Tuesday, February 12, 2013 5:50 PM
To: Midrange Systems Technical Discussion
Subject: RE: how to retrieve records and make sure they arrive in RRN sequence

Is there a reason not to use the ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... ) function?

create table qtemp/memotext as
(
With ALLDATA as (select * from mdcmdct
union
select * from mdcmtx1)
Select ROW_NUMBER() OVER(), ALLDATA.* from ALLDATA
ORDER BY ROW_NUMBER() OVER()
) with data;

You will want to place your ordering criteria within the OVER to determine how it should number. Also, your ROW_NUMBER() doesn't have to be in the selection criteria.


-Tom Stieger
IT Manager
California Fine Wire





-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Tuesday, February 12, 2013 2:33 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL: how to retrieve records and make sure they arrive in RRN sequence

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



create table qtemp/memotext as
(select * from mdcmdct
union
select * from mdcmtx1

) with data;

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.