>>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 >This is nice.