Good day everyone.

We are experienceing some strange results when we perform SELECTS using 'AND'. If we perform a SELECT using the 'AND' operator we are getting incorrect results, but, if we perform the SELECTS separately we get correct results. We have removed all indexing from the table and rebuilt them from scratch, with no success. We are also in the process of checking the records and IDs for non-printable characters and will repair any records or IDs that might contain one of these characters. Does anyone out there have any other insight into this problem?

Thank You

Daniel Rebich

Is it possible that one of the "AND" items is MV'd?. Are you also using a "BY" clause that might be MV'd?

Don M.

Don,

Neither field is an MV field and there is not a 'BY' clause in the SELECT. These are SELECTS that we have been doing for years.

Thanks

Daniel

it might help to give an example of the actul select statement you are using.

jay

Jay,

Here is the SELECT statement and a description of the two fields.

SELECT MASTER WITH LAST.GRP=12345' AND WITH LAST.SUSP=A'

LAST.GRP - Is a single value symbolic that takes the most recent value entered in an MV field,

LAST.SUP - Same as above and has two possible values 'A' or 'S'

Not much to it, but it on;y works if we do each SELECT separately.

thanks

Daniel

If you have been using that select for years and now it's broken, then something has to have changed from the last time it worked until it broke. Find that something and your problem is solved.

Just for the heck of it, why not enclose the two "with" clauses in parenthesis.

I'd also look at the data: write a program to examine each data field to ensure that all values are legal.

Way back in the old days with RevG and DOS had a real stumper of a problem - the main billing report would terminate early. Some sleepless nights later, discovered that their main data entry person was getting Ctrl Z's in the data. Rev didn't mind but the Novell spooler sure did.

Can we see the code for the symbolics? How are you looking at the MV field within the symbolic?

Victor,

Here you go…

X=COUNT(@RECORD,@VM) + 1

@ANS=@RECORD

and…

X=COUNT(@RECORD,@VM) + 1

@ANS=@RECORD

As straight forward as it gets…I think.

thanks

Daniel

What if record 9 or 25 have no data?

Shouldn't it be

x=@record + (@record # '')

In this case, it doesn't matter. If the field has no data, then X will be zero, so the whole field will be used, namely null.

If the symbolic fields are defined as single-valued fields, I don't see the problem.

One other thing. Are the symbolic fields indexed? If so, maybe the indexes are out of sync.

Victor,

In the original topic I was unclear about these two fields. Indeed, they are indexed and we have rebuilt them several times in an attempt to get things working again. We have searched the keys and the data of the entire file for non-printable characters (except for @VM, @FM, etc) and removed any potentially offending characters. Even after taking these steps we are still getting incorrect results. We're certain that it has something to do with the indexing, but, we are not sure what at this point.

PS - The fields will never be null they are required fields.

thanks

dxr

It is generally @Vms and @Fms in keys that mess up indexed searches so first step is to check for these.

The Sprezzatura Group

World Leaders in all things RevSoft

Another thing that I ran across was that the defined length for the key field on the table was not long enough to accomodate the longest key in the file. For example, the key field has a length of 10, but the longest key has a length 15.

This scenario will also corrupt your indexes. When I had this situation, I experienced the exact same problem you did. A select on one index only, and any subsequent selects against the active result list, worked fine. Any select that included multiple indexes in the filter would bomb out.

The other thing that I ran across was watching out for key field synonyms. The key field that is important is the one that is marked as "Master." To be safe, I expanded the defined length on any and all key fields, whether they were master or synonym.

Once I did this, and then rebuilt the indexes, the problem was resolved. Of course, it took me a week and a half of 12-14 hour days to find this, but that's the game I guess.

HTH,

msorrel@greyhound.com

Greyhound Lines, Inc.

Matt,

Thanks for sharing your knowledge. We have spent significant amounts of time, as well, on this problem. We appreciate you sharing your information, since, the time you expended was substantial.

Thanks again.

Daniel

When you say do each select separately do you mean one after the other?

(A & B=your with clauses)

Example:

SELECT A

results, filter active

SELECT B

results, filter active

How many hits?

1) SELECT A and B

2) SELECT A (by itself)

3) SELECT A followed by SELECT B

3) SELECT B (by itself)

What happens if you reverse the order on concurrent selects. SELECT B followed by SELECT A?

Where are your temporary sort files pathed out to in the ARev environment settings? What is the condition of your LISTS and/or SYSTEMP files?

I have a scan program on my website that validates the various parameters for files to insure proper indexing. (on the Downloads page of the website).

Also, you don't say what version you are using. If it's not 3.12 and you are using 4 part keys for any file, the results can be unpredicatable.

Feel free to call.

Larry Wilson

webmaster@AdvancedRevelation.com

972-768-2965

View this thread on the forum...