Thank borislav,

Xcase can't put script in left outer join clause ....

it is not possible with other script ? ( where ? group by ? )


bernhart




>>Hello,
>>
>>I have 2 Tables ( Employee.dbf and Contrat_l.dbf )
>>
>>

>>  CREATE CURSOR Employee ( Iid I  )
>>  CREATE CURSOR Contrat_l ( Iid I  , iemployeeiD I , ddebut D(8) , dfin D(8) )
>>
>>  FOR x = 1 TO 60
>>    INSERT INTO Employee VALUES (x)
>>  ENDFOR
>>
>>
>>
>>  INSERT INTO Contrat_l VALUES ( 1 , 4 , {^2005/03/18}, {^2005/09/15} )
>>  INSERT INTO Contrat_l VALUES ( 2 , 14 , {^2001/06/13}, {^2006/01/12} )
>>  INSERT INTO Contrat_l VALUES ( 2 , 14 , {^2007/01/12}, {} )
>>  INSERT INTO Contrat_l VALUES ( 2 , 22 , {^2003/01/12},  {^2003/09/12} )
>>
>>
>>
>>  SET
>>

>>
>>I want all the employees with or without their contract. The contract (only one) must be the contract of this day.
>>If it is possible without filter in the left join clause because xcase can't do it
>>ans my views are make in xcase program.
>>
>>thank in advance
>>
>>
>>bernhart
>>
>>This code is good but i must make my view with "Create view ...."( left join )
>>
>> *!* FROM Amline!Employee LEFT OUTER JOIN Amline!Contrat_l ON Employee.Iid = Contrat_l.Iemployeeid AND ;
>> *!* DATE() BETWEEN Contrat_l.ddebut AND IIF( EMPTY( Contrat_l.dfin) OR;
>> *!* ISNULL( Contrat_l.dfin ) , DATE() , Contrat_l.dfin ) ;
>
>
>

>CREATE CURSOR Employee ( Iid I  )
>CREATE CURSOR Contrat_l ( Iid I  , iemployeeiD I , ddebut D(8) , dfin D(8) )
>
>FOR x = 1 TO 60
>   INSERT INTO Employee VALUES (x)
>ENDFOR
>INSERT INTO Contrat_l VALUES ( 1 , 4 , {^2005/03/18}, {^2005/09/15} )
>INSERT INTO Contrat_l VALUES ( 2 , 14 , {^2001/06/13}, {^2006/01/12} )
>INSERT INTO Contrat_l VALUES ( 2 , 14 , {^2007/01/12}, {} )
>INSERT INTO Contrat_l VALUES ( 2 , 22 , {^2003/01/12},  {^2003/09/12} )
>lDCurrentDate = DATE()
>SELECT *;
>       FROM Employee;
>       LEFT JOIN Contrat_l ON Employee.Iid = Contrat_l.iemployeeiD AND;
>                              Contrat_l.ddebut <= m.lDCurrentDate  AND;
>                             (Contrat_l.dfin >= m.lDCurrentDate OR;
>                              EMPTY(Contrat_l.dfin )            OR;
>                              ISNULL(Contrat_l.dfin ))
>
>

>
>If you change:
>INSERT INTO Contrat_l VALUES ( 2 , 14 , {^2007/01/12}, {} )
>to
>INSERT INTO Contrat_l VALUES ( 2 , 14 , {^2006/01/12}, {} )
>you will have all emplyees with NULL values for contact except 14 one.