One of my clients just pointed out a problem with a report not including all the records that it should. Upon further investigation, it appears that the BETWEEN clause in the SELECT statement is behaving exclusively, instead of inclusively. I can also duplicate the problem at TCL using the following statement: 'LIST filename WITH DATE_SENT BETWEEN "01 JUN 2005" AND "30 JUN 2005" DATE_SENT'. The results I get show dates from 02 JUN 2005 to 29 JUN 2005, with the first and last dates of the month are omitted…if I substitute 31 MAY 2005 and 01 JUL 2005, I get the expected results for the month of June. I have the same problem when I change my selection criteria to use ]= and =< instead of BETWEEN. DATE_SENT is btree indexed, however I get the same results with a synonym called DATE_SENT_UNINDEXED, which I created to prove this point.

This issue has manifested itself for at least 2 of my clients (I've only checked 2 so far…batting 1000) and both are using OI 7.1.1 runtime, which was upgraded from OI 4.0.3 runtime. I'm not sure if the problem coincided with the upgrade or not. Interestingly enough, when I copy the data onto my development machine (also running OI 7.1.1, updated from 4.0.3), the select works correctly. I wrote a program to look for system delimiters in the keys on the client's machine and none were found.

I noticed that Ray Chan posted a similar problem on February 29, 2004 (sorry, I don't know how to insert a link in my posting). Ray, if you read this, how did you solve it?

I've already invested the better part of a day on this and I am still banging my head against the wall…help!

Thanks in advance,

Gray Cunningham

Gray,

Have you tried using FROM/TO rather than BETWEEN/AND? For example:

SELECT {table} WITH {field} FROM {var} TO {var}?

[email protected] SRP Computer Solutions, Inc.

Hi Frank,

Same problem using the FROM and TO construct.

Slightly off topic here, but since you are intimate with the OIPI: have you noticed that when the print preview window first opens, it claims to be at 100%, but when I change to 125% and back to 100%, the new 100% is larger than the original one? Has this already been identified as a bug?

Gray,

With regards to your selection, there would be two things I would try. One, I'm wonding if it could be an indexing issue. You may want to try a rebuild and/or verify that your field length definition exceeds the data width by at least one. Normally, we use the mm/dd/yyyy format, so we set our field lengths at 11. For your format, you may want to consider 12. Second, have you tried using the internal date format for your selection to see if you get the same results?

On the OIPI - as a matter of fact I did notice that. It is even more noticeable when there are images involved; they look pixelated initially, then look crisp after the resize. I don't know if this is on the current beta bug list, but I will see if I can duplicate it in the new beta and post if necessary.

[email protected] SRP Computer Solutions, Inc.

I had tried an index rebuild, but since the field length is currently 11…I'll try making it 12, just for fun. I am not very confident that this is the problem since I do get the same results using a different, non-indexed, dictionary item that references the same data. I also checked the 2 other clients that use the same software and 1 (that makes 3 in total) has the same problem. The other client and my development system work correctly with the same dictionary item and index.

Gary,

February 2004? Man I wish my memory was that good. I have a hard time remembering what I'm doing today .

But to the best of my ability, I think I just what I found what worked at that time and left it at that and just moved on.

Of course, if there is a problem with "BETWEEN" it should be fixed.

Ray Chan

Just a little note, might help isolate the problem…

The sentence "SELECT FB WITH DATE BETWEEN '1-4-2000' AND '1-10-2000'" works fine for me (OI 7.1.1 no index). I get dates 1-4-2000, 1-5-2000, 1-6-2000, 1-7-2000, 1-8-2000, 1-9-2000 and 1-10-2000.

My "DATE" dictionary does have "D" in the input data validation, and "D/4" in the output data validation.

I have come to the conclusion that this is not a syntax (or Dictionary or Index) problem. I created a new, non-indexed table on my development machine with 2 columns, DATE_OCONV (type DATE w/default conversions) and DATE_INT (type INTEGER w/default conversions), and populated this table with rows containing dates in internal format (the same value in both columns) for the range of 29 MAY 2005 to 17 JUL 2005. When I run list statements from TCL that specify 'DATE_OCONV BETWEEN "01 JUN 2005" AND "30 JUN 2005"', I get the expected results. I also get the expected results when I use GE & LE and ]= & ⇐. I also get the expected results when I say 'DATE_INT BETWEEN "13667" AND "13696"'. HOWEVER, when I launch OI on my client's server and attach this same table, I only get dates between 02 JUN 2005 and 29 JUN 2005, in all the above-mentioned scenarios. I should point out that this issue is not specific to criteria that contain the first and/or last date of a month, but that it ignores the first and last date in whatever range is specified. I did try 'WITH DATE_OCONV=01 JUN 2005"' and that did work.

This is getting increasingly embarassing and difficult to explain to my client and I am running out of things to try. How difficult would it be to reload OI, keeping in mind that this is a runtime that has been upgraded numerous times (since OI 3.6 or 3.7, at least)? Would that even help me, if it can be done? Is there another option? Help!

I also wanted to mention that my Development copy has been upgraded using the same upgrades as the runtime in question.

Also, I don't feel that a workaround (ie: revising my selection criteria to StartDate-1 and EndDate+1) is the answer since this is a fundemental part of OI and I should be able to count on it to work as expected. …it is a mystery, though

Just a possibility - in the SYSENV, ENV*application record, is position 90 set to true ??

Simon

That did it! Good catch, Simon…you're my new best friend! Thanks.

Actually, it was ENV_application, not ENV*application (might have just been a typo), but it now works.

What does column 90 do? I can't find anything that describes it and I can't figure out where it gets set. It does beg the question: why did my development copy and 1 of the 4 runtime copies get set properly and the other 3 runtimes not get set?

I find it ironic that I was just wondering if I should have posted this in the WORKS section (since I wasn't getting many responses, and none from Revelation), when a non-WORKS subscriber solved my problem.

Thanks so much Simon…I owe you one!

Gray Cunningham

Hi Gray,

No problems Glad to be of service.

I cant remember exactly what column 90 is, but I do remember having a similar problem many moons ago - and Sprezz told me about it at that time. It is something to do with case flagging if I remember correctly. I am sure someone will come forward with the proper definition …

Regards,

Simon

Thanks for jogging our memory Simon! Sorry for not jumping in on this but we had naively assumed that this had been fixed as we reported in back in 2002. The exact problem we found was

"A quick check in ARev shows that field 90 is the default case sensitivity flag in ARev

1) There is a bug in GENRTP20_SUB with CASESENS GE & LE settings.

2) The REDUCE code either does not have an IFDEF around the Environ.Constants insert, the 90 is hard coded, or the 90 is equated outside of the insert record.

3) Someone at RTI (most likely a Crafty Programmer or a Gigantic Guru) figured this out and had the 1 placed in field 90 of the SYSPROG environment to mask the problem."

This was after S/List was generating anomalous results and drew upon Aaron's bug fixing work on AREV 3.12. It would seem that the problem still exists - what version are you using?

The Sprezzatura Group

World leaders in all things RevSoft

OI 7.1.1

I upgraded my development version (and my customer's runtimes) from 4.0.3 to 7.1.1 without stopping at each new version, so I can't identify the version where this issue might have manifested itself. I don't think it was there in 4.0.3 as this is the kind of think I tend to test when I write a program. I am puzzled as to why the upgrades to my development copy and one of the runtimes properly flagged column 90 and the other three runtimes failed to get this flag.

View this thread on the forum...