counting records ( not including those marked for deletion)

[coldan]

It seems to me that even with SET DELETED ON the reccount() command returns the absolute number of records.

How do I count only the 'live' records please?

Thanks

coldan

 

[JRB-Bldr]

SET DELETED ON merely hides the visibility of DELETED() records, it does not eliminate them from the table.

There are a number of ways to determine the number of records which are not DELETED()

* -- To get a numerical count of the NOT DELETED() records --
COUNT TO nTotal FOR !DELETED()

* -- or to get only those records which are not DELETED() --
SELECT * FROM MyTable WHERE !DELETED()

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Hi coldan,

it doesn't only seem so it's explicitly said in the help on RECCOUNT(): The value RECCOUNT( ) returns isn't affected by SET DELETED and SET FILTER.

RECCOUNT() does no more or less than read the total reccount from the dbf header, and that does not change when records are deleted or a table is filtered.

You really need to count.

besides
COUNT FOR NOT DELETED

you can also simply
Select Count(*) from table

This only counts undeleted records, if you SET DELETED ON.

What you can do to optimize the performance in VFP9 is have an index NDEX ON DELETED() TAG DeletedTag BINARY. This index will be used by Rushmore to optimize implicit and explicit filtering of deleted records.

JRB-Bldr
You're right with COUNT FOR NOT DELETED

But to SELECT or get only those records, which are not deleted, better simply SELECT * FROM TABLE or USE a table while SET DELETED is ON, don't use DELETED within SQL.

Even though in a simple SQL selecting only from one table you can use DELETED() in a where clause, even with such SQLs it doesn't make a performance difference if you have such a where clause or SET DELETED ON. So please just use the one a simple but strict rule and never use DELETED() in SQL. You might later extend some sql and overlook a DELETED() in there which then can really hurt.

I'd never suggest any SQL with DELETED() in it.

Bye, Olaf.