Hi All

I have a next problem to solve. I know I can do via xBase, but I'm intersted to do it in SQL

given is a set of data lets say "Material" that has to forced to be dealed with. Same material exists several times. We have an unique index to distinct the records

*basic set
CREATE CURSOR curBase (P_IX I,iMat I, nQty N(12,3))
TEXT TO lcTemp NOSHOW
P_IX,iMat,nQty
1,1,2
2,1,3
3,1,4
4,2,0.2
5,2,20
6,2,8
7,2,22
ENDTEXT

STRTOFILE(lcTemp,'Help.csv')
APPEND FROM HELP.CSV TYPE CSV
DELETE FILE HELP.CSV

Now the user wants to deal with the sums of the material,

*summation set
SELECT;
 iMat, SUM(nQty) AS nQty;
 FROM curBase;
 INTO curSum READWRITE;
 GROUP BY 1

Now the user likes to handle the amount or less

*one altered
REPLACE nQty WITH nQty-5

[tricky part]
lets say this material that has to be handed out from a storage. I to hand out the material from the store (we don't care about fine details) but we need to notify for wich record of the base set we hand out

*result cursor
CREATE CURSOR curTarget (P_IX I,iBase I,iMat I, nQty N(12,3))
*iBase points to curBase.P_IX

I have the wear down the basic set biggest qty first (better would be best fitting)

so for the given example (Material 1 qty 4) the set need to be

*Result set
TEXT TO lcTemp NOSHOW
P_IX,iBase,iMat,nQty
1,3,1,4
2,7,2,22
3,5,2,21
4,6,2,8
5,4,1,0.2
ENDTEXT

if I repeat the procsss for qty=4 on Material 1 again it would be

*summation set
REPLACE nQty WITH 0 ALL
LOCATE
REPLACE nQty WITH 4

*input into result set curTarget neede here

*Result set now (old and new data)
TEXT TO lcTemp NOSHOW
P_IX,iMat,nQty
1,3,1,4
2,7,2,22
3,5,2,21
4,6,2,8
5,4,1,0.2
6,2,1,3
7,1,1,1
ENDTEXT

Any idea to do this via SQL INPUT?

TIA

Agnes