You're doing 2 subselects one inside the other. What exactly do you want to achieve? Can you do only 1 JOIN for now?


>Boris,
>
>when i try this... i have a syntax error...?
>
>

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

>
>
>
>>>hi Boris,
>>>
>>>i have a little problem....
>>>
>>>if i want ALL R1.Cnomcustomer but also R1.Cnomcustomer without Etats...
>>>
>>>it's possible ?
>>
>>
>>As Peter said change INNER JOIN to LEFT JOIN:
>>

>>* From
>>....
>>FROM Amline!Resident R1;
>> INNER JOIN
>>...
>>
>>* To:
>>FROM Amline!Resident R1;
>>LEFT JOIN
>>