Peter,
In all my years of FoxPro development, I have
never ever seen FoxPro ignore the SET DELETED status while running a SQL command. If it is ON no deleted record ever makes it into the result. If it is OFF deleted records will be included in the result.
Are you sure that it's not just a matter of you sometimes running the SELECT inside a private datasession where unless you change it SET DELETED is off instead of on.
>I'm wondering... Since long I propagate that SQL-statements should exclude deleted records (if that's what you want) by using WHERE NOT DELETED() in the statement. I have this idea that the Select-SQL reacts to the setting of SET DELETED, but that it's not waterproof, that it may happen that occasionally deleted records will be included anyway, unexpectedly. Therefore, I always exclude them by using WHERE NOT DELETED() explicitly in the statement.
>However, this poses a problem if the Select-SQL gets data from two or more tables, since the alias-argument of DELETED(
) isn't supported as you'd expect. (Rather, it's misleadingly supported, since it may point to a table/cursor that's not involved in the Select-SQL - which uses USE .. AGAIN - but does exist and has a record pointer and thus always returns the same result, True or False.)
>
>I've added an item to the wishlist: Support for use of local alias when referencing functions like DELETED() and RECNO() in SQL-Query Wish #1093
>
>But what I'm now interested in: Have others experienced unexpected results with SET DELETED ON and therefore do the same as I do? Or is everybody happy with using SET DELETED ON preceding the Select-SQL?
