Re: Reversal of Records

A big "THANK YOU" to all who responded. Seems to me to be a good number of experts out there. For the most part, my "little" problem has been solved and the boss is somewhat satisfied. I do however have a few more questions. Quoting Greg Bryan, DHL WORLDWIDE EXPRESS <gbryan@ssf-sys.dhl.com> in X-Informix-List-Id: <list.649>: > A perform screen will retrieve rows ordered on a particular field > under these circumstances: > > 1. The field has an index on it > > 2. A restriction, even a trivial one, is placed on that > field before the query is executed > > To apply this to the serial number: > > 1. Place a unique index on the SERIAL column. > > 2. Instruct the user to place the following restriction > in the SERIAL field on the perform screen prior to > executing the query: > > >0 > > This will include all rows, but will cause perform > to use the index, and present the rows ordered on > the index. > > If your requirement is that this must be transparent to the user, > then PERFORM is the wrong tool - it's a straightforward tradeoff > between flexibility and simplicity between a 4GL screen and perform > screen. Greg, I tried what you suggested above and sure enough it works! But it works only up to a certain point (see below). I also tried it without a unique index. I used a Dups index and it worked just as well. I guess that you merely need to have an index of either kind on the Serial column to get the desired results. I thought I read somewhere that a Serial column cannot have a Dups index (after all, it would not be possible to have duplicates on a Serial column). Is this now a restriction in version 4.00? Nevertheless, our version 2.10 lets me declare a Dups index on the Serial column. I assume the limitation in using the index occurs because all empty slots in the .dat file will be used before the file is increased in size. Here's the results: I. Added four records: 1,2,3,4 II. Removed record 2, then added record 5. III. Query without using the index gave: 1,5,3,4 Query using the index (specified >0 on the serial col): 1,3,4,5 IV. Now added back record 2. Query without using the index gave: 1,5,3,4,2 Query with index: 1,3,4,5,2 QUESTIONS: Why didn't the index retrieve the records in Step IV above so that record 2 would come after record 1? After all, the index was able to put record 5 after 4 (and not after 1 where it is physically located). Why does Perform retrieve the rows in a different order when it is using an idex? I thought indexes were merely supposed to speed the search process and not affect the order in which rows are read. Quoting Tony Heskett (th@bnr.co.uk): > Final answer: > > Sorry, wrong tool. The ace reports will work fine. Perform will > only do what you want if you can force a cluster before a query > (don't sound too multi-user, does it). Apologies if I/we misled you. > > Give them 4GL screens or do it some way that time order isn't > important any more. YES I certainly agree with you Tony. Perform is the wrong tool. What my users really need is a 4GL array. Unfortunately, the 4GL package is not loaded on the machine in question (we have 5 machines and only one has 4GL; the others have SQL). The users will have to get by with Perform for now. Thanks for responding. John Baker =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= John Baker USAISC - Lex Phone: (606) 293-3644 or 293-3743 Lexington - Blue Grass Army Depot DSN: 745-3644 or 745-3743 Lexington, KY 40511-5109 E-mail: jbaker@lexington-emh2.army.mil =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=