Re: Reversal of Records

Hello again. First, thanks to all who responded to my message. All the information was quite helpful. Now for a few quotes and follow-ups (I have listed the results of my experiment last). Quoting Will Hartung (la4gen!villy@uunet.UU.NET): > [ text deleted ] > By putting a serial field on the row, a number is assigned that is sequential > and unique, i.e. in the order of entry. When you later select the rows, you > still must specify the serial field in the ORDER BY clause to have the rows > returned in "entered" order. The use of the index created with the serial > field will make the retrieval more efficient. > [ text deleted ] Will, you have mentioned exactly what I suspected all along after reading the initial responses to my original message. The ORDER BY clause on a serial field *must* be specified in the ACE report or .sql script in order to have the rows returned in their "entered" order. Adding the ORDER BY clause to the Select statement in an ACE report is easily done. Unfortunately, that solves only half of my problem; it still ignores my primary problem. **** EVERYONE PLEASE PAY ATTENTION TO THIS NEXT PARAGRAPH! **** I will restate my original problem. Clarity is difficult to achieve sometimes, so please forgive me if I did not make my problem understandable the first time around. After reading the responses, it seems to me that my primary problem has been ignored by everyone, or at least no one has clearly stated in his/her message that my primary problem is unsolvable. Perhaps that is due to the lack of clarity on my part. Here is a rewording of my problem: Please remember that I am using SQL for this application, not 4GL. I am also not trying to just print out the records in an ACE report (that minor problem has been solved). My biggest problem is trying to get the records displayed by the standard engine in the order entered WHILE RUNNING THE PERFORM SCREEN *after* the user has added and deleted and then added more records. My original problem remains, which is: How do I get the standard engine to use an ORDER BY clause (on a serial field) when it goes out to retrieve records during a "Query" done while running an INFORMIX-SQL PERFORM screen? You see, in 4GL this problem is easily solved. After running the CONSTRUCT statement, you simply append "Order by serial_col" to your select statement that you will PREPARE and then DECLARE a cursor for. In SQL, I don't think there is any way you can affect the operation of the Query option of PERFORM (other than specifying particular search criteria in various screen fields). Tell me if I am right or wrong on this point: In SQL (running PERFORM), the records will be retrieved in the order that C-ISAM has stored them whether or not a) you have a serial column in your table, b) you have declared an index on any column, or c) you have declared a clustered index on any column. In other words, you can't change the way the records will be read when running PERFORM screens (i.e. doing queries). True or false??? Last Thursday, I ran some experiments using PERFORM and came to the above conclusions regarding PERFORM and C-ISAM. Thanks to Walt Hultgren for his explanation of C-ISAM. It does seem to function the way he described it (see below). Quoting Greg Bryan, DHL WORLDWIDE EXPRESS (gbryan@ssf-sys.dhl.com): > [ text deleted ] > The reason for this is the reason that SERIAL exists - it provides > an automatically generated primary key. The only implied > requirements are that a type SERIAL be UNIQUE and NOT NULL. > In addition, a quick review of several Informix 4.0 manuals > indicates that there is no promise made there about ordering > of type SERIAL with respect to INSERT sequence. > [ text deleted ] Greg, please tell us which pages of the 4GL manuals discuss "ordering of type SERIAL with respect to INSERT sequence." I can't find anything. Quoting Walt Hultgren, Emory University, Atlanta, GA <list.618>: > [ text deleted ] > With the linked list approach, when the user "deletes" a row, it is placed > at the end of the list. When the user "adds" a row, the empty row list is > checked first so that the file won't be physically extended unless it is > really full. > Depending on how you implement the list, it's usually easiest (and quickest) > to use the row most recently added to the list in a last-in-first-out > fashion. Thus empty rows will be re-used in the reverse order in which they > were "deleted." > [ text deleted ] This seems to be the kind of strategy that Informix uses. But my experiment indicates that it is not using a simple last-in-first-out ordering scheme. There seems to be some sort of overflow area (or spare slot) that C-ISAM uses to store one of the records (see results below). RESULTS OF EXPERIMENT I am using the SQL PERFORM menu version 2.10. The options on the menu that I used are "Query", "Next", "Previous", "Add", and "Remove". I have listed the steps I followed and the results I got. My experiment was fairly thorough. The table is brand new and empty (remember, having indexes and serial columns makes no difference): 1. I added eight records as follows: 1,2,3,4,5,6,7,8 2. I removed the records one at a time, using the "Remove" option, in this order: 1,2,3,4,5,6,7,8 3. I then added all the records back in, in the order 1,2,3,4,5,6,7,8. "Next/Previous" showed the proper order of 1 through 8. After doing a Query however, I got: 7,6,5,4,3,2,1,8. I don't know why record 8 appeared last. With last-in-first-out, record 8 should have been first, but it wasn't. 4. Then I added record 9, did a "Query" and got: 7,6,5,4,3,2,1,8,9. 5. Removed 4, then 3, then 2. "Next" showed 7,6,5,1,8,9 as we would expect it to. I then added back 2, then 3, then 4. The "Next" option displayed the records in the order 7,6,5,1,8,9,2,3,4 as we would expect it to. After doing a "Query" I got: 7,6,5,4,3,2,1,8,9. 6. Now I removed all the records. Then I added nine new records back in. After doing a "Query" I got: 1,2,3,4,5,6,7,8,9. 7. Removed 9, then 8, then 7. Then I added back 7, then 8, then 9. After doing a "Query" I got: 1,2,3,4,5,6,7,8,9. 8. Removed 1, then 4, then 9. Then added back 1, then 4, then 9. After "Query" I got: 4,2,3,1,5,6,7,8,9. ( Now you see why my users were unhappy! They were ) ( expecting the order to be 1,4,9,2,3,5,6,7,8. ) 9. Removed 4, then 7, then 8. Then added back 4, then 7, then 8. After "Query": 8,2,3,1,5,6,7,4,9. ( What a mess for my users.) =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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-