🚀 go-pugleaf

Steve,

Once again, thanks for your thorough and prompt response. I followed
all your suggestions and the problem still persists, with one twist at
the end. Here is the flow:

>Try your same SQL against another table or, better yet, one of the sample
>tables

Tried on another of my tables with the same zipcode range and count
and sql gave same value. Also tried on Customer.dbf and a count or an
sql statement for a randomly chosen postal range gave same value.

>where data values are shorter than the field length. If you look at a table
>file in a hexidecimal file viewer, you should see nothing but data and

Did this with the table in question and could not find any evidence of
corruption.

>Try copying the contents of your table out to an ASCII file,
>comma-delimited or SDF. How many records get copied out? All 543 or just
>199? Is the zip code field for any of those copied records blank? Are any
>of the zip code field values longer than five (including invisible
>characters)?

Did this and all 543 copied out, all the same length, no spaces or
other inappropriate characters.

>If all of your data is successfully copied to the text file, create a new
>table file using the same structure as the original and copy the data into
>it from the ASCII file. Does *this* copy of your data successfully work
>with your original SQL statement? If so, you will have eradicated some data
>corruption that was interfering.

Did this and the new file still gives the discrepancy between count
and sql statement.

>Another thing you can try is a REPLACE ALL, replacing the field's data with
>the current contents of the field using the dBASE TRIM function. (I suggest

Did this and the discrepancy still existed.

>What would be really great is if you could identify one (or a couple) of
>the 344 records your original SQL statement is having a problem with. Given
>this knowledge, you could look at those records in specific while
>inspecting the table file in a hexidecimal viewer and potentially see the
>source of the problem.

Finally, I did this and here is what I found. If you look at a text
file of the output for a dml command (copy..delim) and compare it with
a listing of the record set from the sql statement, what becomes
evident is that the records being excluded in the sql statement are
those with the same value for the zipcode field. For example, in the
copy..delim file, there may be 7 records with zipcodes of '94010' but
in the sql record set there is only one.

It appears, therefore, that the sql statement (select * from <table>
where zipcode >= '94000' and zipcode <= '96200') may be giving a
record set as if the unique modifier was being used? But, when I
tested this possibility against another table or the customer table,
where dml and sql gave similar values, this was not the case.

I realize you believe the table in question is the problem, but I have
done all I know to rule this out and cannot find a problem. You
mentioned about getting a data corruption utility, but I don't know of
one that will work on level 7 tables. Nevertheless, I would have
thought the manipulations I have done (above) would have cleared up
all but the worst of problems. And, if a problem still existed then it
must be serious, certainly too serious to allow use of the table?

Any other suggestions?

Ned