Original question:

Using ARev 3.12, but have experienced same problem on 2.01 & 2.03 (having upgraded from 2.03 to 3.12 some time ago). Have a select statement SELECT TABLE WITH some criteria, then a secondary SELECT (the same) TABLE WITH some other criteria, then a sort SELECT (the same) TABLE BY AFIELD BY ANOTHERFIELD BY ATHIRDFIELD

Problem is during the SELECT BY part it gets this error. If you take the by clause down to one field, any of the fields, it works. And to make it worse, this problem only happens once in a great while. Then we get past it by changing the first select to grab a lessor set of data and do the process in batches. Problem goes away for a while, then maybe one day it comes back.

The sort fields are a 6 char alphanumeric field, an unlimited length text field (a name field), and a date field. Have taken off all indexes and still have the problem (again, randomly).

Could it be a problem with the length of some data in the name field? Would truncating that field in a symbolic formula and using that in the sort make a difference?

So Far, per suggestions, I have made sure that the row FS100 does indeed exist in the SYSMESSAGES table, , have made sure that the !FileName row is present in the !FileName file, have taken off all indexes on the table.

Then I tried truncating the name field in the sort to 20 chars (it is a symbolic formula so just added @ANS=@ANS1,20 to the end of the formula) and that worked. This time. Other times taking off the indexes worked but not this time so I am suspect about today's solution of truncating the name field in the sort to 20 chars.

Any more ideas?

This sounds a bit like ritual abuse of indexes.

Why not simply write a symbolic which returns

a 1 if the selection criteria are met, otherwise 0.

Then select on the symbolic.

Otherwise, check that there are no delimiters in

your keys, and that an index.flush(,) is

performed ahead of your selection.

Steve.

After the second select, do a save-list. Get the list back, then do the by select. When it breaks, examine the saved list and try and see if you can determine which record the error occurs on. It could be a matter of timing in that a transient record makes the list and is later deleted.

akaplan@sprezzatura.com

Sprezzatura Group

Thank you, Aaron Kaplan, for the suggestion that the problem might be due to a row being deleted in between the select and the sort statements. However, this is not the case since I was able to reproduce it over and over on a standalone database where I was the only user on a local drive.

Thank you, Steve Smith, for the suggestions about indexing, but the problem may occur even with all indexing taken off, so performing an index.flush(,) would not help. Especially when (once it comes up) the problem can be reproduced over and over until the database is changed to produce a different result set (doing the process in batches to update the data so that less is selected each time). There are no delimiters on the keys; the keys are sequential numbers. And the problem occurs after all selection is done, and a separate statement to sort the selected data is performed.

The problem only happens once in a great while, on different databases, during a process that is performed monthly on hundreds of installed sites. But 99.99% of the time it works fine, even on the same systems that had the problem one time.

Still Stumped,

Dona McElroy

mcelroy@comtronicsys.com

Sorting large files will make extensive use of your sort directory. Make sure that it exists as defined in your environment and that you have adequate disk space. This probably isn't your problem, but I thought I'd suggest it just in case.

View this thread on the forum...