Hi Liam (and Jim and David),

>Hi Peter,
>
>I've never had a problem with SET DELETED. It has always worked as expected for me.

As I said, I was wondering.. whether or not my 'knowledge' on this point was correct. The reactions up till now indicate that I've coded SQL-queries for a long time with wrong knowledge about this aspect. Although.. if the query uses a UDF that changes the setting..., but that would be a badly programmed UDF of course.

A thought that comes to mind now is that the reaction to SET DELETED is inconsistent with the reaction to SET FILTER. No matter what filter is active, the SQL-query will ignore the filter anyway.


>I wouldn't agree with you that it's "misleadingly supported". MS is very clear about it : Be careful, you may get unexpected results.

Well, they'd have done us a better service if they'd explained it more extensively. For example a text like:
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 will always returns the same result.

>Regards,
>
>Liam
>
>
>>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.