> SELECT R1.Cnomteleph ,; > R1.Iid ,; > CAST( Cor9.IetatId AS I ) AS IetatId , ; > CAST( Cor9.nType AS I ) AS nType ,; > Cor9.ccat_Physiq , ; > cor9.ddebut,; > cor9.Ltransfert,; > CAST( 0 AS N( 2, 0) ) AS nposcatetat,; > CAST( '' AS C(5) ) AS cetats, ; > CAST( CTOT(" - - T00:00:00") AS T ) AS ddepart, ; > CAST( CTOT(" - - T00:00:00") AS T ) AS dretour; > FROM Resident R1 LEFT OUTER JOIN ( SELECT E.iresidentId , ; > NVL( E.nType, 0 ) AS nType ,; > E.iid AS IetatId ,; > E.ccat_Physiq,; > NVL( E.lTransfert , .F. ) AS lTransfert ,; > E.ddebut ; > FROM Etats E inner JOIN (; > SELECT IresidentId,; > MAX(IID) AS IId; > FROM Etats GROUP BY IresidentId ) Etst ; > ON E.IresidentId = Etst.IresidentId AND E.IId = Etst.IID) Cor9 ; > WHERE R1.iid = cor9.iresidentId ; > AND EMPTY( TTOD( R1.dsortie ) ) ORDER BY R1.Cnomteleph INTO CURSOR temp > >Try this
SELECT R1.Cnomcustomer ,; R1.Iid ,; Cor9.IetatId , ; Cor9.nType ,; Cor9.ccat_Physiq , ; cor9.ddebut,; cor9.Ltransfert; FROM Amline!Resident R1; LEFT 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 temp