Sergey,

in fact i have still a problem.

FUNCTION test145()

  CREATE CURSOR AgendaPhar  ( nstkjour N (6,2) , itraiterId I , iagendadateid I )

  DIMENSION cSample[ 14 , 3 ]
  C= 0

  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1743
  cSample[ c, 3 ] =  1
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1800
  cSample[ c, 3 ] =  1
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1748
  cSample[ c, 3 ] =  1
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1749
  cSample[ c, 3 ] =  2
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1743
  cSample[ c, 3 ] =  2
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1748
  cSample[ c, 3 ] =  2
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1800
  cSample[ c, 3 ] =  2
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1901
  cSample[ c, 3 ] =  2
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  2043
  cSample[ c, 3 ] =  2
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1743
  cSample[ c, 3 ] =  3
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1748
  cSample[ c, 3 ] =  3
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1800
  cSample[ c, 3 ] =  3
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  1901
  cSample[ c, 3 ] =  3
  C=C+1
  cSample[ c, 1 ] =  0
  cSample[ c, 2 ] =  2043
  cSample[ c, 3 ] =  3


  SELECT AgendaPhar
  APPEND FROM ARRAY cSample






  FOR vp_id = 1 TO 3

       UPDATE AgendaPhar  ;
      SET nstkjour = NVL( pd.nStkjourCor , 0 ) +1 ;
      FROM AgendaPhar;
      LEFT JOIN(;
      SELECT apd.nstkjour AS nStkjourCor , apd.itraiterId ;
      FROM  AgendaPhar apd ;
      WHERE apd.iagendadateid < vp_id ) pd ;
      ON AgendaPhar.itraiterId = pd.itraiterId AND ;
      AgendaPhar.iagendadateid = vp_id

  NEXT

  BROWSE NOWAIT
  SET

ENDFUNC

For itraiterId i want have

itraiterId nstkjour iagendaId
1743 1 1 && It is just
1743 1 2 && It is false want i want 2
1743 2 3 && it is false want I want 3

A idea ?


bernhart


>You have to use LEFT JOIN in order to upadte all records in the v_AgendaPhar. With INNER JOIN the records in v_AgendaPhar that don't have matching records in derived table pd will not be updated.
>

>  UPDATE v_AgendaPhar  ;
>      SET nstkjour = NVL( pd.nStkjourCor , 0 ) +1 ;
>    FROM v_AgendaPhar;
>      LEFT JOIN(;
>        SELECT apd.nstkjour AS nStkjourCor , apd.itraiterId ;
>          FROM  AgendaPhar apd ;
>          WHERE apd.iagendadateid < vp_id ) pd ;
>    ON v_AgendaPhar.itraiterId = pd.itraiterId
>

>
>
>>
>>I have many problems to know the stock of previous days.
>>I want for the day ( iagendadateid = 2 ) the stock of ( iagendadateid = 1 ) + 1
>>
><snip>