Does using Joinkeys always change Order of the Final File ?

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

I have , say 100 accounts on File A and 20 accounts on File B .
I need FileC with those accounts that are present only on File A and missing in File B (80 Accounts).

A Join unpaired on File A only will give me the desired result after I match on accounts .

However I see that the Accounts in File C are not in the same order as it was in File A (after removing the 20 accounts of file B) .

Is there a way I can ensure that records from File A are written to output FileC "as is" without any change of order ?

Bill Woodger · Posted: Wed Nov 07, 2012 10:03 pm

To do the JOINKEYS correctly the file must be in order based on the key(s) for matching. By default, JOINKEYS (both of them) sort the data with OPTION EQUALS.

If you want the original order, you can add a sequence number to the record, and in the main-task following the JOINKEYS sort on the sequence number.

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

Thanks Bill....I will add a sequence number at the end of the original file and use that to restore the order of the file after the Join .

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

Or RESORT the file in the original desired order (Account Number) without having to extend file length temporarily. It really depends on how much benefit you are getting by sorting on sequence number vs. account number. If size of the account number is less than that of sequence number that you need to add, it may be beneficial to just sort on the account number in stead.

Thanks,

Akatsukami · Posted: Wed Nov 07, 2012 11:15 pm

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

Yes, I used account number for the example because that is the only field provided here. What I was trying to say is "if" the size of the "original order/desired order" is less than that of sequence number, OP may benefit from resorting in the "original order/desired order".

Thanks,

Bill Woodger · Posted: Thu Nov 08, 2012 12:02 am

However, it would depend on whether or not the original keys were unique.

If not unique, it then depends on whether NOEQUALS was used.

If not unique and NOEQUALS was used then the only way to get to the original order, other than by fluke, is with the sequence number.

If EQUALS was used then a SORT with EQUALS on the original key should get back to the same order (with the JOINKEYS using EQUALS). The need for EQUALS adjusts the comparison for estimated resource usage beyond a simple one of length.

If the keys are unique, then certainly a SORT with NOEQUALS will also guarantee the original order.

After all this, I hope that it is a testing file rather than a production file that only has 80 records.

Puspojit · New User Joined: 10 Mar 2008 Posts: 34 Location: Pune

I would need to add sequence number since the accounts are stored in the file in the "order" that they were created in the Firm and I dont want to lose that order by sorting .

Again, the layout of this file is not the best, that I can use any other pointers within the file to maintain that chronological order, apart from creation of a sequencer number .

Thanks for all your suggestions ! It really helps !

regards
Puspojit