SELECT S.Clm_Id, S.Svc_Dt, P.Active_Ind ; FROM Mc_MdSvc S, Mc_MdClm C ; LEFT JOIN Mc_PlnHs P ON ; P.Mem_Num = C.Mem_Num ; AND P.Active_Ind = .T. ; AND P.Pln_Id = C.Pln_Id ; AND P.Ins_Id_Num = C.Ins_Id_Num ; AND P.Eff_From <= S.Svc_Dt ; AND P.Eff_Thru >= S.Svc_Dt ; WHERE C.Adjt_Stat = "Z" ; AND C.Clm_Id = S.Clm_Id ; HAVING ISNULL(P.Active_Ind)Caveat: I doubt this will work :) VFP is pretty forgiving about what you can put in the LEFT JOIN clause, but ANSI standard SQL disallows anything but = in a JOIN ON clause ... and if VFP could handle my formulation, I'd think it could handle yours as well.
>SELECT S.Clm_Id, S.Svc_Dt ; > FROM Mc_MdSvc S, Mc_MdClm C ; > WHERE C.Adjt_Stat = "Z" ; > AND C.Clm_Id = S.Clm_Id ; > AND NOT EXISTS (SELECT * FROM Mc_PlnHs P ; > WHERE P.Mem_Num = C.Mem_Num ; > AND P.Active_Ind = .T. ; > AND P.Pln_Id = C.Pln_Id ; > AND P.Ins_Id_Num = C.Ins_Id_Num ; > AND P.Eff_From <= S.Svc_Dt ; > AND P.Eff_Thru >= S.Svc_Dt) >>What it chokes on is the last 2 lines of the subquery. It only can handle equality. Any Ideas?