If you want ALL records from Employee table - no.


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