>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
>

Hey, that's a good description <s>

hth
-Stefan

SELECT ;
	T1.code, T1.name, ;
	SUM(Distinct T2.qty), ;
	( SELECT SUM(T3.credit) ;
		FROM table1 T3 ;
		WHERE T3.code2 = T1.code ;
		AND SUBSTR(code,4,1)='1' ) as amt1, ;
	( SELECT SUM(T4.credit) ;
		FROM table1 T4 ;
		WHERE T4.code2 = T1.code ;
		AND SUBSTR(code,4,1)='2' ) as amt2 ;
	FROM table1 T1 ;
	LEFT JOIN table1 T2 ;
		ON T2.code2 = T1.code ;
	WHERE LEFT(NVL(T1.code,''),3) = '140' ;
	GROUP BY 1, 2