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