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