L
Lieven De Foor
Guest
Hi, I want to bring something to the attention of the community as I think this is valuable information. When a query/for each is using a non-unique index to iterate records, Progress ensures a consistent sorting of the records with identical index values. It seems like the ROWID of the record is silently appended as extra segment of the index in that case. E.g. If you do FOR EACH Customer BY SalesRep, all Customers having the same SalesRep will always be displayed in the same order, and not mixed in each run of the query. Also, when reversing the BY clause, they are still consistently sorted in reverse order. While this looks like a sound solution, we have a case where this causes very strange, yet perfectly explainable, behavior. Problems can be seen when creating a temp-table having a non-unique index, e.g by creating it LIKE a db table (inheriting structure and indexes from the db table). In that case the order in which the records are created in the temp-table influences the way they will get iterated when performing a query using the non-unique index. To demonstrate this, I've written a small procedure using the sports2000 db to demonstrate this. The procedure will get the LAST 50 records by using a DESCENDING sort order. This DESCENDING sort however is the trigger to the strange behaviour... In the resulting output you see several records switched places, even though the same data, BY clause and indexes are used on both the temp-table and the db table. DEFINE VARIABLE Counter AS INTEGER NO-UNDO. DEFINE VARIABLE DbOrder AS CHARACTER NO-UNDO INITIAL "DbOrder:~n". DEFINE VARIABLE TTOrder AS CHARACTER NO-UNDO INITIAL "TTOrder:~n". DEFINE TEMP-TABLE tt_Customer LIKE Customer. DEFINE QUERY CustomerQuery FOR Customer. /* --------------------------------------------------------------------- */ OPEN QUERY CustomerQuery FOR EACH Customer NO-LOCK BY Customer.Comments DESCENDING. DO WHILE TRUE: GET NEXT CustomerQuery. IF QUERY-OFF-END("CustomerQuery") OR Counter = 50 THEN LEAVE. CREATE tt_Customer. BUFFER-COPY Customer TO tt_Customer. ASSIGN Counter = Counter + 1 DbOrder = DbOrder + "~n":U + STRING(Customer.Name, "X(20)") + " " + STRING(ROWID(Customer)). END. FOR EACH tt_Customer BY tt_Customer.Comments DESCENDING: ASSIGN TTOrder = TTOrder + "~n":U + STRING(tt_Customer.Name, "X(20)") + " " + STRING(ROWID(tt_Customer)). END. OUTPUT TO "C:\Temp\DbOrder.txt". PUT UNFORMATTED DbOrder. OUTPUT CLOSE. OUTPUT TO "C:\Temp\TTOrder.txt". PUT UNFORMATTED TTOrder. OUTPUT CLOSE. MESSAGE "Output in C:\Temp\DbOrder.txt and C:\Temp\TTOrder.txt" VIEW-AS ALERT-BOX. Yikes! To work around this my suggestion is to add a Rowid field to the temp-table and have it contain the rowid value of the db record. This Rowid field should be added as last segment in the non-unique index of the temp-table, making it unique and resulting in the expected order (i.e. the same order as the fields in the db). Btw, this problem also manifests with unique indexes containing null values...
Continue reading...
Continue reading...