>>>No, you can not use a DELETED('alias') reference in a SQL select statement. You can use a DELETED()
> reference as long as there is only one table involved in the SELECT.
> SELECT * FROM junk WHERE DELETED('junk')
> will return no records, even if there are records marked for delete, where:
> SELECT * FROM junk WHERE DELETED()
> will return deleted records.<<
>
>You missed something here. When you use this statement, always bear in your mind that 'DELETED' value int the environment is set to 'OFF'. Better read the 'SET DELETED' statement. Since you tried my tricked and failed, then try again by issuing the 'SET DELE ON' statement before the SQL-STATEMENT.


I know about SET DELETED ON. There are times where it is useful to use the DELETED() statement in an SQL, especially if what you want is ONLY the deleted records. Try that without it! All SET DELETED OFF will do is allow you to ignore the deleted status, meaning you'll get back both records that are and are not deleted. You can't get back just the deleted ones. I think you are the one that needs to read the SET DELETED help again.

My point is that you can not specify an alias within the DELETED() function in a SQL statement. Your example showed that.

>set deleted off
>select field1,field2 from table1 where not(deleted('table1')) into cursor cursor1
>set deleted on

Note that you can not use ANY VFP function that requires an alias or a work area in a SQL statement, like RECCOUNT('alias'), etc. Without the alias is OK, if the command will work that way.