>create cursor temp(vou_no n(6), acc_code c(7),party c(1),qty n(8),rate n(7,2),cr_amount n(15,2)) > >insert into temp(vou_no,acc_code,party,qty,rate); > values(3322,'3201003','A',50,460) > >insert into temp(vou_no,acc_code,party,qty,rate); > values(3322,'3202001','B',95,410) > >insert into temp(vou_no,acc_code,party,qty,rate); > values(3322,'3206003','C',1,1415) > >insert into temp(vou_no,acc_code,party,qty,rate); > values(3322,'3207001','D',2,1415) > >insert into temp(vou_no,acc_code,party,qty,rate); > values(3323,'3201003','A',20,460) > >insert into temp(vou_no,acc_code,party,qty,rate); > values(3323,'3202001','B',25,410) >>
> >select vou_no,acc_code,party,sum(qty)as qty,sum(qty*rate)as cr_amount; > from temp; > order by vou_no,party; > group by vou_no,party; > into cursor temp2 >>
create cursor temp(vou_no n(6), acc_code c(7),party c(1),qty n(8),rate n(7,2),cr_amount n(15,2)) insert into temp(vou_no,acc_code,party,qty,rate); values(3322,'3201003','A',50,460) insert into temp(vou_no,acc_code,party,qty,rate); values(3322,'3202001','B',95,410) insert into temp(vou_no,acc_code,party,qty,rate); values(3322,'3206003','C',1,1415) insert into temp(vou_no,acc_code,party,qty,rate); values(3322,'3207001','D',2,1415) insert into temp(vou_no,acc_code,party,qty,rate); values(3323,'3201003','A',20,460) insert into temp(vou_no,acc_code,party,qty,rate); values(3323,'3202001','B',25,410) SELECT DISTINCT Party FROM Temp INTO CURSOR crsDist lcSQL = "SELECT vou_no" SCAN lcSQL = lcSQL + ", SUM(IIF(party = '"+Party+"[', qty,0)) AS "+ALLTRIM(Party) ENDSCAN lcSQL = lcSQL + ", SUM(qty*rate) AS cr_amount" lcSQL = lcSQL + " FROM Temp" lcSQL = lcSQL + " GROUP BY vou_no" lcSQL = lcSQL + " ORDER BY vou_no" lcSQL = lcSQL + " INTO CURSOR crsTest" &lcSQL BROWSE NORMAL