Hi Inge,
You got incorrect result because the functions DELETED(), EOF(), FOUND(), RECCOUNT() and RECNO(), which support an optional alias, could return unexpected results in multiple-table queries.
I think, you should reconsider your design. The deleted records should be treated by your application as none-existent. Imagine, that you're writing your application against Sql Server where records are gone as soon as they are deleted.
If you need to keep some records for reporting or other purpose, either mark them with some flag or move them to the separate history table.
>Help!
>
>SELECT last,first,enter,left,st,myOtherTable.descr;
> FROM myTable INNER JOIN myOtherTable ;
> ON myTable.id=myOtherTable.id ;
> WHERE (ENTER<=CTOD('02/01/02') AND UPPER(St300.st)="A" AND DELETED()=.F.) ;
> OR (LEFT>CTOD('02/01/02') AND ST300.ST='I' AND DELETED()=.T.))
>
>
>where DELETED() refers to whether or not the corresponding record in myTable is marked for deletion.
>
>The above statement does not work. Does anybody know a workaround?
>Thanks all!
--sb--