Hi Jerry,

Wouldn't this work? (If you do not need more fields from Table2 you can do just ignore the second join and)

SELECT		Table1.*, ;
		A.*, ;
		Table2.* ;
	FROM	Table1 ;
	LEFT	OUTER JOIN	( ;
		SELECT		MAX(B.effdt) as maxeffdt, ;
				B.SetID, ;
				B.DeptID ;
			FROM	Table2 B ;
			WHERE	B.effdt < Table1.effd ;
			GROUP	BY B.SetID, B.DeptID ;
			) A ON Table1.setid = A.setid AND Table1.deptid = A.deptid ;
	LEFT	OUTER JOIN Table2 ON Table1.setid = Table2.setid AND Table1.deptid = Table2.deptid AND Table2.effdt = A.maxeffdt

>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