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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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.