>>>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.

Fred was right about using an alias in the Deleted() function. Mike explained it by saying SQL Select statements open up the source tables again under new aliases in different workareas. One of the reasons for this is so the record pointer does not move in the original tables.

If your where clause uses an alias, then the SQL statement checks the original alias for the function result. You can have two outcomes. If the original alias is positioned on a non-deleted record, then you will get no records in your result set because the function will return .F. every time it is checked. If the current record is deleted, then you will get every record because the deleted() function will always return .T..

It may have been brought up before (I haven't followed the entire thread), but you could use a subquery to get this information.

select * from test1, test2 ;
   where test1.field1 = test2.field1 ;
      and test1.key in (select key from test1 where deleted()) ;
   into cursor c_result