Kbase P116656: Break BY option on FOR EACH changes main component sort order
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
27/05/2011 |
|
Status: Verified
SYMPTOM(s):
Break BY option on FOR EACH changes main component sort order
Break BY option on FOR EACH changes sort order
Adding a BY clause to a FOR EACH statement changes the sort order of the main component of the sort group.
For example the following two FOR EACH staments return different sort orders of the main component of the sort keyOfString. Adding the BY clause for sequence_num changes the keyOfString sort, when it is expected that it only changes the sequence_num sort within keyOfString.
FOR EACH xl_string_info WHERE XL_string_info.KeyOfString BEGINS "Factur"
USE-INDEX STRING_Key BREAK BY XL_string_info.KeyOfString :
DISPLAY XL_string_info.KeyOfString XL_string_info.sequence_num.
END.
FOR EACH xl_string_info WHERE XL_string_info.KeyOfString BEGINS "Factur"
USE-INDEX STRING_Key BREAK BY XL_string_info.KeyOfString
BY XL_string_info.sequence_num:
DISPLAY XL_string_info.KeyOfString XL_string_info.sequence_num.
END.
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
CAUSE:
The cause of this behaviour is because the client is resorting the records when the second BY clause is added. This is a TranMan database, but the same thing applies to any client connection to a database.
Since this is a TranMan database the code page of the database is "undefined" and its collation is "TRANMAN". This is correct for a TranMan database and should never be used for anything else. When the first query is performed the database index String_key selects the records and passes these back to the client. The client does nothing with these records other than display them in the sequence the database index selected them.
Then when the second BY clause is added, the index cannot satisfy this new order so the records have to be passed to the client, where they are re-sorted according to the client collation. This will almost certainly not be "TRANMAN", so there is a different order when the records are displayed.
If the code is compiled code with the XREF option, in the XREF file for the second code block there are "SORT-ACCESS" entries. This indicates the sorting that is required for the second block but not for the first.
As a test, start your client with:
prowin32.exe -cpinternal undefined -cpstream undefined -cpcoll TRANMAN
and run the code again. In this session the results will be exactly the same for both blocks of code. As mentioned above, undefined should never be used as a client code page and TRANMAN as a client collation.
FIX:
Set the client collation to a valid (not TRANMAN) collation, where the records will be sorted correctly.