>>>First, SET DELETED is OFF. Why does the following SQL not work?

>>>select cust_num, emp_num from customer ;
>>>   where customer.emp_num in ;
>>>   (select empid from employee where deleted())

>>
>>HOw do you expect DELETED() to know which alias to check DELETED in? I made this mistake before, trying to use DELETED in a SQL query, and found that you can only depend on it when there is one and only one table involved in the query, so SLQ doesn't get confused. Also, specifying the alias: DELETED("Employee") doesn't work either... apparently something along the line breaks when you try to mix the VFP DML (DELETED()) with SQL.
>
>Yeah, I had also tried deleted("employee") to no avail. I do however expect the subquery to run first and for SQL to properly interpret a simple and explicit select contained within the parentheses. Looks fairly straightforward to me.
>
>Bottom line, I was just making sure I had not overlooked some obvious error in my syntax. However, I still see no reason why this should not work. I am going to try a more creative way later tonight.

Hi Mark,

You'll probably have to do this with an extra SELECT statement.

select empid from employee where deleted() into cursor longgone
select cust_num, emp_num from customer ;
  where customer.emp_num in ;
  (select empid from longgone)

Erik is right, SQL gets confused when there's more than 1 table involved, and you can't specify an alias on those kinds of functions in a SQL, either. I was told this was because internally, SQL uses a separate copy of your table with it's own alias's.