Update: FYI, this is a known “issue” and works this way by default. See comments for more details.

Maybe I’m missing something (and I hope I am). The following condition exists in VFP7 through to VFP9SP1 (and maybe earlier than 7). I don’t remember ever reading anything about it either.

This one is hard to explain (and believe), so you may need to run the example code to see what I mean. And right off the bat: This issue has nothing to do with the RECCOUNT/_TALLY results we all know about regarding deleted records (RECCOUNT always returns the number of rows in the table regardless of the SET DELETED setting). No, this issue has something to do with an index and the current setting of DELETED. When an index is set on a column involved in a WHERE clause of an SQL statement, RECCOUNT will return the total number of records from the ‘FROM’ table instead of the results from the cursor when SET DELETED is OFF! It seems as though the resulting cursor has a bunch of deleted records (the scrollbar in the browse window only goes half-way up (if you know what I mean)), but they can’t be accessed regardless of the SET DELETED setting.

The problem is with code like this:

SET DELETED OFF
SELECT sometable
SET INDEX ON somefield   && can exist in a CDX too
SELECT * FROM sometable WHERE somefield = somevalue INTO CURSOR crTemp
? RECCOUNT('crTemp')   && wrong! RECCOUNT returns the wrong reccount!
? _TALLY               && correct results

Here’s a complete example, using a table called ‘name_list’, which has a column for name c(30) and some deleted records. In the first example, the table name_list does NOT have an index on ‘name’. RECCOUNT and _TALLY return expected results.

USE name_list IN 0 EXCL 
 
SET DELETED OFF
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF:  "
?? _TALLY              && 81,019
?? RECCOUNT('crName')  && 81,019
 
SET DELETED ON
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE ON: "
?? _TALLY              && 80,951
?? RECCOUNT('crName')  && 80,951

Now, add an index to the name column and be amazed:

USE name_list IN 0 EXCL
INDEX ON name TAG name
 
SET DELETED OFF
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF:  "
?? _TALLY              && 81,019
?? RECCOUNT('crName')  && 126,607 -- the actual number of records in name_list!?!
 
SET DELETED ON
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE ON: "
?? _TALLY              && 80,951
?? RECCOUNT('crName')  && 80,951

In the above example, it doesn’t matter if the index is set to name (SET ORDER TO name) or not! Just the fact that the index exists throws off RECCOUNT(). If you do a count on crName with DELETED OFF, you get the expected results:

USE name_list IN 0 EXCL
INDEX ON name TAG name
 
SET DELETED OFF
 
SELECT name FROM name_list WHERE name>='H' INTO CURSOR crName
? "WITH DELE OFF:  "
?? _TALLY              && 81,019
?? RECCOUNT('crName')  && 126,607 -- the actual number of records in name_list!?!
COUNT TO ln
?? ln                  && 81,019

The moral is not to use RECCOUNT() to get the total number of records from
an SQL statement. Always use _TALLY. But unfortunately, this undocumented behavior has certainly crept into someone’s code somewhere. Consider a situation where you want to test to see if the SQL returns any records. You might do something like this:

SELECT * FROM sometable WHERE somefield = somevalue INTO CURSOR crTemp
IF RECCOUNT() > 0
  ...
ENDIF

This code is fatal if in fact there happens to be an index on somefield and SET DELETED is OFF! If anyone has some insight, I’d love to hear it…

Tags: Debugging, VFP