DELETED() Shows Incorrect Information

[TotalBeginner]

On a table this command stores 2191 to the z_old_deleted variable.

COUNT FOR DELETED() TO z_old_deleted

However this code only produces a cursor with 1342 records:

SELECT RECNO() FROM order_header WHERE DELETED() INTO CURSOR cur_deleted READWRITE

No filters are running. The code is run one line after another. I do have an index on DELETED().

 

[TotalBeginner]

Furthermore the following code produces two different results:

COUNT FOR DELETED() TO z_old_deleted NOOPTIMIZE
? z_old_deleted [result = 1342]
COUNT FOR DELETED() TO z_old_deleted
? z_old_deleted [result = 2191] (the correct figure)

How can I go through to determine the 2192 deleted records?

 

[mspratt]

Any duplicates?

Regards,

Mike

 

[TotalBeginner]

No Duplicates, just actual records that need deleting.

 

[Stella740pl]

I've just tested it. Opened a table with under 200 records, and deleted 8 of them at the end.

Now, no index on DELETED() exist.

SET DELETED OFF
COUNT FOR DELETED() TO z_deleted && result is 8
SELECT RECNO() FROM MyTable WHERE DELETED() INTO CURSOR cur_deleted && result is 8

Now,

INDEX ON DELETED() TAG Deleted

SET DELETED OFF
COUNT FOR DELETED() TO z_deleted && result is 0 !!!
SELECT RECNO() FROM MyTable WHERE DELETED() INTO CURSOR cur_deleted && result is 8

So, the problem lies somewhere with COUNT and INDEX ON DELETED() together. It's weird.

 

[Stella740pl]

There is more to it.
COUNT works correctly (result is 8) when ORDER is SET to Deleted TAG. If order is not set to this TAG, the result of COUNT is 0.

 

[Stella740pl]

Either the situation has more to it, or I did something wrong the first time. I deleted a few more records from the beginning of the table, and cannot reproduce it any more.

 

[Mike Gagnon]

Check you setting on 'set deleted'

CREATE CURSOR mycursor (name c(20))
INSERT INTO mycursor (name) VALUES ("mike")
INSERT INTO mycursor (name) VALUES ("paul")
INSERT INTO mycursor (name) VALUES ("frank")
delete for name = 'paul'
set deleted off
COUNT FOR DELETED()  && One record deleted
SET DELETED ON
COUNT FOR DELETED() && No records show as deleted

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Olaf Doschke]

Well, SET DELETED ON set's a default filtering "DELETED() = .F.", so counting deleted records while DELETED is set ON should always count 0. Browse behaves the same:

Select table && has some deleted records in it
SET DELETED ON
BROWSE FOR Deleted() &&results in an empty sheet
SET DELETED OFF
BROWSE FOR Deleted() &&shows the deleted records.

If you need a correct count on deleted records always SET DELETED OFF.

Concerning the problem with PACK in your earlier thread, I'd say you have a coupled problem with the DELETED setting and an index on deleted() that may be currupted.

You may try to get the correct PACK result with creating four tables with the COPY TO command I gave you:
a) set deleted on before copy to
b) set deleted off before copy to
c) a) plus drop the index on deleted() beforehand
d) b) plus drop the index on deleted() beforehand

I'd say you'll get the correct result with set deleted off and not dropping (but setting/activating!) the index beforehand, because as you say the index points to the right records. Nevertheless the index is defect if you get a different count when the index is dropped. So maybe the index is corrupt but as you always showed the data with this index set it represents the deleted records correctly, although the normal deletion mark says something different. After copiing the created table should have the right records, then drop the index on deleted() you copied (COPY TO ... WITH CDX) and rebuild it, afterwards index and real deleted records should be in sync.

Bye, Olaf.

 

[alvechurchdata]

SELECT RECNO() FROM order_header WHERE DELETED() INTO CURSOR cur_deleted READWRITE

DELETED() may be referring to the wrong work area. Try

SELECT RECNO() FROM order_header WHERE DELETED([b]order_header[/b]) INTO CURSOR cur_deleted READWRITE

Geoff Franklin

http://www.alvechurchdata.co.uk