Hi to all,

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?

Groet,
Peter de Valença

Constructive frustration is the breeding ground of genius.
If there’s no willingness to moderate for the sake of good debate, then I have no willingness to debate at all.
Let's develop superb standards that will end the holy wars.
"There are three types of people: Alphas and Betas", said the beta decisively.
If you find this message rude or offensive or stupid, please take a step away from the keyboard and try to think calmly about an eventual a possible alternative explanation of my message.