How to show deleted Records?

[abbasaif]

Hi,

While running the following query failed to get the required records. The intention is mentioned in subject.

Please guide me.

Thanks

Abbasaif

SELECT Somain.sono, Somain.sodate, Somain.duedate, Somain.pcode,;
  Customer.full_name, Somain.location, Somain.cashname, Somain.enteredby,;
  Somain.salesman, Somain.merchand, Somain.lpo, Somain.zone,;
  Somain.discamount, Somain.discrem, Somain.remarks, Somain.marks,;
  Somain.sprem, Sosub.icode, Sosub.vatperc, Sosub.qntymaj, Sosub.qntymin,;
  Sosub.ratemaj, Sosub.ratemin ;
 FROM ;
     village!SoMain ;
    INNER JOIN village!SoSub ;
   ON  Somain.sono = Sosub.sono ;
    INNER JOIN village!Customer ;
   ON  Somain.pcode = Customer.pcode;
   Where Deleted() ;
 INTO CURSOR ViewSo READWRITE NOFILTER

 

[Mike Gagnon]

Check to see what the SET DELETED is set to.



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

 

[abbasaif]

set deleted is "OFF"

Thanks

Abbasaif

 

[rubinov]

Probably "where deleted()" is confusing not only to me (deleted where, in what table?).

Try:

select ... from (select * from table1 where deleted()) _1;
inner join (select * from table2 where deleted()) _2 on _1...=_2...

 

[mjcmkrsr]

Hi,

I can't offer a solution, just wanted to point out a strange behavior of DELETED(). The record seems to lose the deleted flag asa it gets involved into a JOIN (see code below)

SET DELETED OFF 

SELECT 1

CREATE CURSOR tblNames (cName C(10), nID I)

INSERT INTO tblNames (cName, nID) VALUES ("Michi", 10)
INSERT INTO tblNames (cName, nID) VALUES ("Mecky", 11)
INSERT INTO tblNames (cName, nID) VALUES ("Myaer", 12)
INSERT INTO tblNames (cName, nID) VALUES ("Johnny", 13)
INSERT INTO tblNames (cName, nID) VALUES ("Zicky" , 14)

LOCATE

DELETE Record 3

BROWSE 

SELECT 2

CREATE CURSOR tblANames (cName C(10), nID I)

INSERT INTO tblANames (cName, nID) VALUES ("James", 10)
INSERT INTO tblANames (cName, nID) VALUES ("Johnny", 11)
INSERT INTO tblANames (cName, nID) VALUES ("Mia", 12)
INSERT INTO tblANames (cName, nID) VALUES ("Farler", 13)
INSERT INTO tblANames (cName, nID) VALUES ("Fanny" , 14)

LOCATE

DELETE Record 3

BROWSE 

SELECT * FROM tblNames T1;
	INNER JOIN tblANames T2 ON ;
		T1.nID = T2.nID ;
	INTO CURSOR csrNames

BROWSE 
CLOSE ALL
CLEAR ALL

hth

MarK

 

[Olaf Doschke]

You're not the first trying ans asking and it doesn't work out that way or in any good way.

What rubinov suggests works and you can decide per table whether you want to replace it with a subquery addressing only its deleted rows or only the non deleted rows, but there is no general inversion of only concentrating on the deleted rows.

If you ask me it's just gracious of the sql engine to allow usage of DELETED() in single table queries or subqueries. The only real reason for it is that a) really deleting rows from a DBF file would take in average rewriting half the DBF or as a faster compromise that would mess with chronological order swap deleted and last record and then truncate the file before this last record.

So SQL should only be used in SET DELETE ON mode.

This hack to use SELECT * FROM sometable WHERE DELETED() to only look at deleted rows are not really optimizable. As DELETED() only has two values and often is more .F. than .T. you have an unbalanced binary tree index on DELETED(), using the optimized BINARY index type for boolean values that index tag will be much more compact, but Rushmore optimization won't use it.

The help recommends index FÓR DELETED() which only have deleted rows in the index itself. Again general SQL isn't using them as SQL opens up DBFs again and then any SET FILTER or SET ORDER you do on them has no effect, so you also can't create an INDEX ON '*' TAG xDel FOR DELETED() and then SET ORDER TO xDEL and query on that workarea. BROWSE a table set to this index and you only see deleted rows, but query from it, and SQL reopens the workarea again and only Rushmore will decide whether to use indexes on that new workarea.

SQL does not take into account SET FILTER, too. Same reasoning. So just stop it. Even in the fashion it works partly.

What do you really need? If you need the deleted customers to query them, move them to a table you call deletedcustomers before deleting them and then join that table instead. with the help of TRIGGERS that can even be done automatically. And within trigger code you can make use of SCATTER/GATHER to copy a row. Extrremely easy as both tables have the samee structure, same fields.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

MarK,

that behavior is simply to see. SET DELETED OFF means VFP disregards the deleted flag of rows unless you specifically ask for it via DELETED(). locating a deleted record is your only chance of DELETED() to be .T. in SET DELETED ON the deletion mark is an automatic FILTER of records.

When that filter is gone all rows are getting into query results.

SET DELETED ON/OFF does not switch between only undeleted and only deleted rows it switches between respecting the flag or not respecting it, so between the table as it would be with removed deleted rows and the table with ALL rows, also deleted rows.

So the original idea to use WHERE DELETED() is not bad, but it doesn't work that simple.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Olaf,

Thanks for the explanation - I guessed something like this - you spoke it out.

MarK

 

[mjcmkrsr]

Hi Abbasaif

You may try rubinov's suggestion or have a look at the code below

SET DELETED OFF 

CREATE CURSOR tblNames (cName C(10), nID I)

INSERT INTO tblNames (cName, nID) VALUES ("Michi", 10)
INSERT INTO tblNames (cName, nID) VALUES ("Mecky", 11)
INSERT INTO tblNames (cName, nID) VALUES ("Myaer", 12)
INSERT INTO tblNames (cName, nID) VALUES ("Johnny", 13)
INSERT INTO tblNames (cName, nID) VALUES ("Zicky" , 14)

DELETE Record 3

BROWSE 

CREATE CURSOR tblANames (cName C(10), nID I)

INSERT INTO tblANames (cName, nID) VALUES ("James", 10)
INSERT INTO tblANames (cName, nID) VALUES ("Johnny", 11)
INSERT INTO tblANames (cName, nID) VALUES ("Mia", 12)
INSERT INTO tblANames (cName, nID) VALUES ("Farler", 13)
INSERT INTO tblANames (cName, nID) VALUES ("Fanny" , 14)

DELETE Record 4

BROWSE 

SELECT DELETED() as lT1Deleted, * FROM tblNames T1;
	INNER JOIN (select deleted() as lT2Deleted, * from tblANames) T2 ON ;
		T1.nID = T2.nID ;
	INTO CURSOR csrNames

BROWSE FOR lT1Deleted OR lT2Deleted
CLOSE ALL
CLEAR ALL

hth

MarK

 

[Olaf Doschke]

What do you really want to see and why, Abbasaif?

The problem with DELETED OFF and joins is the deleted flag is lost in the query result. In the result all rows are combined and have DELETED()=.F., of course, they are new rows with a new row structure and combined columns. The WHERE DELETED() clause isn't working wonders to only take deleted rows of any table into account, and what's most important in that aspect, you rarely have the situation all records belonging together by a join condition are deleted.

For example,an order item is deleted from an order that doesn't delete the order itself, the deleted order item has no meaning anymore besides it once was an order item and the customer decided against ordering it. And not only that, if you could inverse the available data to only deleted data, the deleted order item has no deleted order and thus will not appear in the inverse query, as it has no parent order, the order isn't existing undeleted and deleted at the same time, even if it would be Schrödinger's order.

Because of that the typical status is a mix of deleted and undeleted records of a join and therefore as MarK last suggested to turn DELETED() into a real field is the best you may do and then you can BROWSE for any such field being .T. as MarK suggested or just pick one or just display it as info, it'll likely only be for administrative data maintenance anyway.

The bad side effect of deleted rows can be to have a gap in numbering. You can fill that with the next record instead of continuing with the max+1 value, but not if you have a primary or candidate index on that column. So you could turn such fields to their negative value before deletion. But that also doesn't solve that problem for more than one reuse. Imagine you store the sortorder of order items. When you remove the item with sortorder 2 you turn that to -2 and then can reuse the 2, but with a very unsure customer deleting that item again, you get a double -2. The solution here is easy, you don't ever renumber items, you just iterate the records in ascending sortorder and in a report don't print sortorder, but a report variable initialized to 1 and incrementing. To be able to sort in a new item between existing ones is a problem also if no item is deleted at all. You have to renumber all order items then. And in a DBF you have to do that including deleted rows.

In short, you have to deal with the problems arising from deleted rows being kept in the DBF and more important in this case, also in the index tags. Defining all indexes FOR !DELETED() is also a viable idea to not also have the constraint of unique deleted numbers like IDs or sortorders. But before you redefine all your indexes, maybe you find an easier solution.

It's natural once you can see deleted rows in browse with that deletion mark in DELETED OFF mode it would be torture to not be able to undelete it, in other databases you don't have that luxury at all, but developers and users can cope with that, too. All features added to VFP like RECALL and the DELETED() function and are merely a compromise because of the DBF structure.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[abbasaif]

Hi, Mr. Olaf

What do you really want to see and why, Abbasaif?

The management just wanted to see, how many Orders have been deleted in the given period.

And, in order to keep in mind the "strange behavior of DELETED()" as quoted by mjcmkrsr, I applied the following:

Select * From somain Where Deleted() And SoDate Between dt1 And dt2 Into Cursor tSomain Readwrite

Select ;
   tSomain.Sono, ;
   tSomain.SoDate, ;
   tSomain.DueDate,;
   tSomain.pcode, ;
   Customer.full_name,;
   tSomain.location, ;
   tSomain.cashname, ;
   tSomain.enteredby, ;
   tSomain.salesman, ;
   tSomain.merchand, ;
   tSomain.lpo, ;
   tSomain.zone, ;
   tSomain.discamount,;
   tSomain.discrem, ;
   tSomain.remarks, ;
   tSomain.marks, ;
   tSomain.sprem, ;
   SoSub.icode,;
   SoSub.vatperc,;
   SoSub.qntymaj, ;
   SoSub.qntymin, ;
   SoSub.ratemaj, ;
   SoSub.ratemin, ;
   SoSub.QavalMajSd, ;
   SoSub.QavalMinSd  ;
   FROM ;
   tSomain ;
   INNER Join village!SoSub ;
   ON  tSomain.Sono = SoSub.Sono ;
   INNER Join village!Customer ;
   ON  tSomain.pcode = Customer.pcode;
   INTO Cursor ViewSo Readwrite

Regards

Abbasaif

 

[Olaf Doschke]

>The management just wanted to see, how many Orders have been deleted in the given period.
That also bears the question of who deletes orders and why. As a customer of any shop I may cancel my orders before they are fulfilled. If that's when you DELETE orders, think about an orderstatus flag or more fully-fledged an order status history table that notes the state changes from submitted over partially to fully fulfilled, returns (on the order item levesl) and also canceled. Then you'd not need such a look into deleted rows.

Well, "§strane" has been clarified, I hope. In short you switch between undeleted and all records.

And yes, to detect deleted orders you'll mainly only look for DELETED() in the main order table. Drilling down from that root level all order details are about lost revenue. There's more detail to it, though, when customers already deleted some items while they put together the original order you count that as revenue loss, while such items were never having the submitted order status. Another reason you don't maintain such information by keeping deleted records and in fact disallow deleting any orders and order detail data but instead use your own flags.

Are you sure you never PACKED the tables and lost information overall? You might be by being the only with VFP being able to do so at all.

In general youd keep track of such things as additional meta data not only for the reason of how "strange" the deletion flag works, but also to have more detail info about the steps and chronology and reasons of revenue loss with more detailed status info then the deletion flag.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Hi Abbasaif,

I want to add one more remark to all those Olaf already made. Your code gives a wrong impression of which records are deleted in which tables. If you stick to this approach you'll have at least to create three cursors with the deleted records and join them, (see code below - assuming "SoMain" is your main table) - or consider Rubinov's suggestion.

Select * From Somain Where Deleted() And SoDate Between dt1 And dt2 Into Cursor CSR_1 
Select Full_Name From Customer Where Deleted() And ... Into Cursor CSR_2 
Select * From SoSub Where Deleted() And ... Into Cursor CSR_3 


Select ;

...

   FROM CSR_1
        Join CSR_3 ;
             ON  CSR_1.Sono = CSR_3.Sono ;
        Join CSR_2 ;
            ON  CSR_1.pcode = CSR_2.pcode;
   INTO Cursor csrDeletedRcords

hth

MarK

 

[Olaf Doschke]

MarK,

technically correct. But if a parent record of orders is deleted, all child data is orphaned. One normal strategy to avoid orphaned data is cascading deletes, another is to hinder deletions unless child data is deleted first. In both strategies child data will end up deleted. And so if you don't apply either strategy by not implementing referential integrity (or not using a DBC in the first place, which allows that) you likely have mixed states.

That means you don't find the full data of an order in the deleted part of data and thus the main ingredient is starting at the deleted head records. That's fine. And the way you suggested joining subqueries of all data with lT1deleted and lT2 deleted fields gives the best overview of the detail states.

Nevertheless, you always have some data remaining unrelated aside from the path of order/order items, because deleting an order obviously doesn't delete customers and products. So to get the data of a deleted order you will join deleted order head, deleted details and undeleted associated data with a larger scope than the order anyway.

Overall, the deletion flag is not very useful information about the state of orders and details about them, therefore the best solution still is keeping track of the status and other chronology, in the sense of double bookkeeping. The deleted status of records is only half as helpful and technically not easy to handle.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

A detail-problem you can't decide with the deleted flag:

First only one order item was deleted, later the whole order. LEt's say now you have a deletied=.t. flag in that deleted item and the order. What is the lost revenue?
Actually it is the order minus the deleted item, it's only all items when you interpret the maximum order as the expected revenue. Then already the backtracking of one item is a revenue loss, even if the customer never submitted it, that item was only in his shopping cart but not really ordered.

Only if your database doesn't just reflect the order itself but also all the changes you have an overview of what you want to really know.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[abbasaif]

Are you sure you never PACKED the tables and lost information overall? You might be by being the only with VFP being able to do so at all.

Yes, Mr. Olaf, I have to take care of not to pack. I am re-thinking not to delete the records and I should open a logical field instead, like
"InvCanc" and will generate the index based on it.

Regards

Saif