Dear Professors
Create Cursor table1 ;
(code Char(7), name Char(30), qty n(4), credit N(12), Debit N(12), code2 Char(7))
Insert Into table1 Values ('1401001','Eric',0,0,100,'3201001')
Insert Into table1 Values ('3201001','Apple',10,100,0,'1401001')
Insert Into table1 Values ('1401001','Eric',0,0,200,'3202001')
Insert Into table1 Values ('3202001','Potato',20,200,0,'1401001')
Total qty=30
Amount1=100
Amount1=200
Insert Into table1 Values ('1401002','Boris',0,0,500,'3201003')
Insert Into table1 Values ('3201003','Mango',50,500,0,'1401002')
Insert Into table1 Values ('1401002','Boris',0,0,300,'3202001')
Insert Into table1 Values ('3202001','Juice',20,300,0,'1401002')
Total qty=70
Amount1=500
Amount1=300
The result must be look like this
--code---name-----qty---amt1--amt2
1401001--Eric-----30----100----200
1401002--Boris---70----500----300
qty column displays wrong resutl in both rows
amt2 column displays wrong resutl as 500 in both rows
only amt1 column displays correct result in both rows
SET ENGINEBEHAVIOR 70
select t1.code,t1.name,;
sum(t2.qty) as qty,;
sum(iif(left(t2.code,4)='3201',t2.credit,0)) as amt1,;
sum(iif(left(t2.code,4)='3202',t2.credit,0)) as amt2 ;
from table1 t1;
left join table1 t2 on t1.code2 = t2.code ;
where LEFT(t1.code,2)<>'32';
group by t1.code;
into cursor group_wise_sale
select group_wise_sale
brow