>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
>
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')
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')
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 AND t2.code2=t1.code ;
where LEFT(t1.code,2)<>'32';
group by t1.code, t1.name ;
into cursor group_wise_sale
select group_wise_sale
browCetin
