Thank sergey.

I have a another problem with a another sql command.

 vp_date = DATE()
* I want the stock From yesterday
*-- If not exist in agendaPhar i get the stock from Traiter.dbf

UPDATE AgendaPhar ;
      SET ;
      NVL( nstkjour = (  SELECT  apcor.nstkjour FROM AgendaPhar apcor ;
      INNER JOIN Traiter Tcor ON Tcor.iid = apcor.itraiterId WHERE apcor.ddate < vp_date ) , 0 )

This command give me a error when i try to save the programm.
Invalid use of a visual foxpro function...

A idea ?
A very big thank for your help.

bernhart




>Try (not tested)
>

>UPDATE ap
>	    SET ltransfert = E.ltransfert
>	FROM AgendaPhar ap
>		JOIN Traiter T ON T.iid = ap.itraiterId
>		JOIN Etats E ON T.Iresidentid = E.Iresidentid
>		JOIN DatesFiltred D ON D.ddate BETWEEN E.ddebut AND EVL( E.dfin , vp_date )
>    WHERE E.ltransfert
>

>>
>>
>>Here is my command.
>>
>>

>>
>>SET ENGINEBEHAVIOR 90
>>
>>UPDATE AgendaPhar;
>>    SET ltransfert = ;
>>    NVL( ( SELECT E.ltransfert ;
>>    FROM  Traiter T INNER JOIN Etats E ON  T.Iresidentid = E.Iresidentid AND T.iid = AgendaPhar.itraiterId;
>>    INNER JOIN DatesFiltred D ON D.ddate BETW E.ddebut  AND EVL( E.dfin , vp_date );
>>    WHERE E.ltransfert = .T. ) , .F. )
>>

>>
>>I want that my subquery is single.( one record )
>>
>>If i try a Group by claus , it is not possible ...
>>What can i do ?
>>
>>
>>
>>Thank in advance
>>
>>bernhart