>To All,
>
>I have a piece of code that runs perfectly in Oracle environment and now want to bring it to VFP,
>but getting error correlating fields:
>
>SELECT Table1.*,;
> Table2.*;
> FROM Table1 LEFT OUTER JOIN Table2 A;
> ON Table1.setid = A.setid AND Table1.deptid = A.deptid;
> WHERE A.effdt = (SELECT MAX(effdt) FROM Table2 B;
> WHERE B.setid=A.setid AND B.deptid=A.deptid ;
> AND B.effdt less then Table1.effdt)
>
>
>I am trying to connect Table 1 to Table2 and select only 1 record from Table2 (effective dated) that is prior to effective date in Table1.
>
>Tried to change ENGINEBEHAVIOUR, but the error exist in all.
>What I am doing wrong?
>
>Thanks

It does not sound like an easy way in VFP, unfortunately. I suggest to scan the table 1 and get the top 1 record from table 2 for each record.

In SQL Server this will be a piece of cake with new OUTER APPLY operator.