>>> >>>SET ENGINEBEHAVIOR 90 >>> >>> m.cfiledata = "C:\cdbk90\amline\data2\" >>> USE ( m.cfiledata + "AMLINE!Resident") IN 0 >>> USE ( m.cfiledata + "AMLINE!Etats") IN 0 >>> >>> >>> SELECT R1.Cnomcustomer ,; >>> R1.Iid ,; >>> Cor9.IetatId , ; >>> Cor9.nType ,; >>> Cor9.ccat_Physiq , ; >>> cor9.ddebut,; >>> cor9.Ltransfert; >>> FROM Amline!Resident R1, ; >>> ( SELECT E.iresidentId , ; >>> MAX( NVL( E.nType, 0 )) AS nType ,; >>> MAX( E.iid ) AS IetatId ,; >>> MAX(E.ccat_Physiq) AS ccat_Physiq,; >>> CAST( MAX( NVL( E.lTransfert , .F. ) ) AS L ) AS lTransfert ,; >>> MAX( E.ddebut ) AS ddebut; >>> FROM Etats E GROUP BY E.iresidentId ) AS Cor9 ; >>> WHERE R1.iid = cor9.iresidentId ; >>> AND EMPTY( TTOD( R1.dsortie ) ) ORDER BY R1.Cnomcustomer INTO CURSOR temp >>> >>> >>> SELECT temp >>> >>> >>> BROWSE NOWAIT >>> >>> SELECT etats >>> SET FILTER TO iresidentid = temp.iid >>> >>> BROWSE NOWAIT >>> >>>>>
SELECT R1.Cnomcustomer ,; R1.Iid ,; Cor9.IetatId , ; Cor9.nType ,; Cor9.ccat_Physiq , ; cor9.ddebut,; cor9.Ltransfert; FROM Amline!Resident R1; INNER JOIN (SELECT Etats.iresidentId , ; NVL( Etats.nType, 0 ) AS nType ,; Etats.iid AS IetatId ,; Etats.ccat_Physiq AS ccat_Physiq,; NVL( Etats.lTransfert , .F. ) AS lTransfert ,; Etats.ddebut AS ddebut; FROM Etats; INNER JOIN (SELECT IresidentId,; MAX(IID) AS IId; FROM Etats; GROUP BY IresidentId) Etst ON Etats.IresidentId = Etst.IresidentId AND; Etats.IId = Etst.IID) Cor9 ; ON R1.iid = cor9.iresidentId ; WHERE EMPTY( TTOD( R1.dsortie ) ); ORDER BY R1.Cnomcustomer; INTO CURSOR tempNOT TESTED!