hi Boris,

i have a little problem....

if i want ALL R1.Cnomcustomer but also R1.Cnomcustomer without Etats...

it's possible ?





>Try this:
>

>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 temp
>

>
>NOT TESTED!