SQL and SET DELETE OFF 2

[titoneon]

Hi Everyone,
I want to find the deleted records that are common to two tables based on common key field on each table, let me give you a little detail
Mainasbly table has a field named mainpk, type= integer autoincrement 4
subassbly table " " " " mainfk type= integer 4
so the mainfk value, it is always the same as mainpk
Ok, so i have this SQL as below shown and it does not show the deleted records

Select t1.mainpk, t1.job_no, t1.empfk, t2.mainfk, t2.workstart;
FROM mainassbly t1, subassbly t2;
WHERE t1.mainpk = t2.mainfk and DELETED() INTO Cursor junkresult

if i leave "DELETE()" and remove the "subassbly t2 and the t1.mainpk = t2.mainfk and" from the SQL CODE, then it will show me the delete records on mainassbly, of course i also removed the fields from subassbly from the code, but actually what i want is be able to run this sql to recall the records deleted from both tables, if run the code as above and remove the "delete(), i can get all the values displayed from both tables, it is something wrong in the code, or it has to do with the "SET DELETE OFF" and the sql ?
Thanks a lot in advance

 

[Mike Lewis]

what i want is be able to run this sql to recall the records deleted from both tables,

Your aim is to restore all deleted records from both tables?

If it's that simple, you could do this instead:

SELECT mainassbly 
RECALL ALL
SELECT subassbly 
RECALL ALL

Presumably it's more complicated than that, otherwise you wouldn't have bothered with the SQL.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just in case it's not as simple as using RECALL ALL ....

Are you aware that, when you run a SELECT on tables with deleted records, and SET DELETED is OFF, then any records in the result set will not appear as deleted - even if it they were deleted in the original tables?

From the VFP Help for SET DELETED:

Calling SQL SELECT with SET DELETED OFF includes records fitting the specified criteria in the result set and sets those records as available for processing. That is, SET DELETED OFF is not set for the records in the result set.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

The solution to your problem is easy, you don't even need to fiddle with SET DELETED OFF.

But to explain what happens, if you query, while SET DELETED OFF: This simply makes VFP ignore the deletion falg, all records are handled as undeleted. Working with DELETED() inside a query is bad, once you query on two, because you never can influence which tables deleted flag is taken. The SQL engine makes use of the workareas behind the scenes and uses different temporary aliases than you, even if a table already is open. Therefore you even can't work with DELETED("t1") or DELETED("t2"). You can't really query the deletion flag, it's not meant for that usage, you may do so in simple single table queries, but even there with a self join you can get unexpected results. Also, the result will never have deleted recordss in it, as the result set is a cursor, a new (temp) dbf, which does not "inherit" the delteion flag of records selected or even joined (and what should it be if you join an undeleted and a deleted record, anyway)?

If you need the deletion status in a query, the best way is to make it part of the normal fields in the first place, eg add a lDeleted field and set it .F. as default and set it .T. before you delete a record. Then you can query with t1.lDeleted or t2.lDeleted

The concept of the deletion flag merely is a necessity, as it would slow down DBFs miseerably, if deleted records would need to be physically removed from the dbf file. To make use of the flag in queries is something you shouldn't do anyway. In other DBMS you simply can't access deleted records anymore, they are deleted. Treat the DBF the same way. If you need access to earlier data, you should do as in other DBMS, either you never delete data and add some field "invalidated", "stornated" or anything like that, or put it into a log or archive data and delete it.

Bye, Olaf.

 

[titoneon]

Hi Mike,
Thanks for getting back, yes it is not just recall all what i need, the reason is that i have a form where i can delete records but if for any reason i want to recall a particular record or more than one record i would like to put in a grid all the records deleted and then from there select the record i want to recall, of course i don't want in the grid the deleted column bit i would like like a check box in a column and then i can mark wich record i want to recall, the issue with set delete off not showing them i knew it but the problem is that when i browse from the sql code i don't any record displayed, in my case there are two records that match in each table as deleted, i don't know why if this has to do with the "WHERE t1.mainpk = t2.mainfk and DELETED()", the problem is does not find a match cause no records are displayed, as you can see as i said the mainpk is always the same value as mainfk, i am using this to normalize the tables, i don't realize what is wrong
Thanks a lot

 

[Mike Lewis]

OK, if I've understood this right, you need to stick with your original query, but with SET DELETED OFF. That will get the deleted records into the cursor, although at that point, they will not appear as deleted (for the reasons that Olaf explained).

Then, use that cursor to populate your grid. When the user selects a record to recall, just navigate to that record in the base table (based on an ID or other unique value), and recall it in the usual way. Actually, it's a little more complicated than that, because there are two tables involved, but the general principle holds good.

Having said all that, I agree with Olaf that ideally you shouldn't run queries based on the deletion flag. If I was in your shoes, I would have a specific field in the table to indicate the deletion status. I'd also think twice about whether I want to give users the ability to recall deleted records - but that's another discussion.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Ok, thanks a lot to both

 

[mplaza]

Try:

Select ;
t1.*, t2.workstart ;
from ;
(select mainpk, job_no, empfk from mainassbly where deleted() ) as t1 join subassbly t2;
WHERE t1.mainpk = t2.mainfk INTO Cursor junkresult

Marco