Here's a strange one: on our client's production AREV system, a query like:

SELECT TRANSACTIONS WITH TRAN.DATE ]= "01-01-99"

will return no records, while the query

SELECT TRANSACTIONS WITH TRAN.DATE ] "12-31-98"

returns a few hundred. Note that TRAN.DATE is an indexed field. Meanwhile, the development system (separate AREV.EXE, separate but similar data files) returns the data correctly in either case. I tried rebuilding all XREF and BTREE indexes in case it was index corruption, but the problem is still there. Could it be that the something in the production REVBOOT directory (like VERBS) has become corrupted?

Peter Fisera

Synergy Computer Consulting

Peter,

Have you tried modifying the select to use a 4 digit year instead of a 2 digit year?

Are the results still the same?

Matt

Check that the field used to build the index is right justified. Also, make sure the conversion is done by the conversion entry in the dictionary rather than being performed within the formula (if it is a symbolic field). Maybe you have keys with delimiters somewhere. You may want to also run a test where you list all records in the file by that date field (using the index), and then check to see if it is actually in order. Is your field a multivalued field?

Matt -

Sorry about that, my example was sloppy. I WAS using 4-digit years when we actually tried this.

Peter

Victor -

Thanks for the suggestions, however, NONE of them helped. The field TRAN.DATE is a single-valued symbolic, right-justified, with the date conversion done via the output pattern rather than the symbolic code. Selecting the entire file sorted by that field and then reading each record resulted in a non-decreasing sequence of values for TRAN.DATE, so the index doesn't appear to be out of order. Also, there were no keys containing delimiters that I could find. I tried using both a PERFORM SELECT and an R/BASIC SELECT (on the file handle) and then a READNEXT loop. If a key existed with delimiters in it (especially @FM, which would be really serious), would READNEXT return it as normal? Is there any other way to check for keys containing delimiters? And have you got any other suggestions as to what could be wrong with this index?

Peter

Peter,

I have experienced the same problem in our application. I have found the problem to be exhibited in some conditions of retrieval of records using Btree indexes and always with a GE or LE (]= or ⇐) qualifier. I have worked around it by soliciting date ranges in collector windows, adding or subtracting from the date range extremes and using the GT or LT as appropriate.

It is a kludge that works. I called RTI about this in 1992 without luck in getting it resolved. I am using AREV 2.11.

I'll be looking for some of the gurus here to offer a better solution. I don't seem to remember having the problem with other than date fields but then, 98% of our queries are date-oriented.

Good luck!

The answer to your prob is really quite simple.

Most selects using date fields have a tendency to not return

the currect values from the database. If you are using a date

format of 010198. (All Numbers)

I dont know the reason for this but I do know that

100% correct every time is the following

JAN 01 1999

do all your selects with date=JAN 01 1999' and ya cant go wrong

Terry Rainville

terryr@istar.ca

The field is a symbolic Index.

Does it require information from another file to calculate.

If so it will never work correctly, see arev manual about

indexing symbolics.

Terry Rainville

terryr@istar.ca

It's because there is some logic to determine if the selection was specified in internal or external format. Using your first example, the date could be interpreted as an internal date rather than the external date that was intended.

What's the version? Does adding the CASEINSENS keyword change things? How about the CASESENS? What about switching the default sensitivity in the environment and trying all three again?

akaplan@sprezzatura.com

Sprezzatura, Inc.

Terry -

Actually, I believe that if there are dashes in date (and quotes around it) AREV could not possibly misinterpret it as a number (ie: an internal AREVdate). However, its all a moot point because the example I gave in my original posting was not accurate. I WAS expressing the dates as "JAN 1 1999" and still getting the strange behaviour. See the posting from JPRYOR for the real answer.

Peter Fisera

Synergy Computer Consulting

Terry -

Yes, I'm quite aware of that problem, and it was one of the first things I checked for. This symbolic however, only uses values from the current record. I was also getting similar behaviour from another indexed date field that wasn't even a symbolic.

Peter Fisera

Synergy Computer Consulting

Aaron -

My version of AREV is 2.11, and the environment is set to case-insensitive queries as the default. If you include the word CASESENS in the query example I used, it does return the correct data, but takes 100 times as long because it is not using the index. If I use the keyword CASEINSENS it has no effect, because this is already the default.

Someone else has seen the same symptoms as me, see the posting from JPRYOR. (glad to know I'm not crazy!)

Peter Fisera

Synergy Computer Consulting

Yes, I think you've got it! I tried that solution (we had thought of that one already, but had decided it was too kludgy a workaround and there must be a better way) and of course it works. Glad to know that someone else has seen these same symptoms. I am ALSO on version 2.11… I wonder if this bug has been fixed, and an upgrade would help?

So the crux of the matter is: less-than, greater-than or between are ok, but less-than-equal-to or greater-than-equal-to will SOMETIMES cause problems. Maybe it only happens for dates in years ending in '9', and that's why it didn't show up until now? (I'm not being facetious here, I tried that query for ]= Dec 31 1998" and it returned data as it should).

Peter Fisera

Synergy Computer Consulting

I think if you set the environment to run with case-sensitivity and add CASEINSENS to all your selects, it fixes the problem.

This is a known bug with some versions. I can't remember exactly when it was fixed.

akaplan@sprezzatura.com

Sprezzatura, Inc.

View this thread on the forum...