hi,
In my report I want to sort on summary column. Pl. suggest any easy way I am using d2k reports 2.5. My query is like this..
Select b.compcode,b.branchcd,b.seriescd,customercd,b.aono,aodt,aotype, paymenttermcd,decode(ldflag,'Y',' LD Applicable') ldflag, porefno||' Dt. '||porefdt pono,
b.slno,b.productcategory,
b.producttype,b.productsize,b.stroke,b.actuate1,b.actuate2,b.mount1,b.mount2,
b.mount3,b.mount4,b.mount5,b.special, (taxedprvalue) aovalue,
sum(ORDERQTY) ordqty, sum(b.DELIVERYQTY) delqty,sum(BASICPRICE) basicprice, sum(nvl(discountper,0)) disc,
sum((basicprice*(c.deliveryqty-nvl(c.despatchqty,0))) - ((basicprice*(c.deliveryqty-nvl(c.despatchqty,0)))*nvl(discountper,0))/100) netsale,
sum(basicprice*(c.deliveryqty-nvl(c.despatchqty,0))) val,
sum(c.deliveryqty-nvl(c.despatchqty,0)) pendqty, c.deliverydt, /*nvl(c.revised_shedule,to_date(c.deliverydt,'dd-mon-rrrr'))*/ (c.revised_shedule) rev_shed,
RTrim(b.ProductType)||
Decode(Rpad(b.ProductSize,8),'','','/'||RTrim(b.ProductSize))||
Decode(Rpad(b.Actuate1,8),'NIL ','','/'||RTrim(b.Actuate1))||
Decode(Rpad(b.Actuate2,8),'NIL ','','/'||RTrim(b.Actuate2))||
Decode(b.Stroke,0,'','/'||b.Stroke)||
Decode(b.Mount1,'NN','','/'||RTrim(b.Mount1))||
Decode(b.Mount2,'NN','','/'||RTrim(b.Mount2))||
Decode(b.Mount3,'NN','','/'||RTrim(b.Mount3))||
Decode(b.Mount4,'NN','','/'||RTrim(b.Mount4))||
Decode(b.Mount5,'NN','','/'||RTrim(b.Mount5))||
Decode(b.Special,'NIL','','/'||RTrim(b.Special))Product
From Saao0 a, saao1 b, saao2 c
Where a.compcode=b.compcode
and a.branchcd=b.branchcd
and a.aono=b.aono
and a.seriescd=b.seriescd
and b.seriescd in('AO','SAO','A2E','ASC')
and a.branchcd='A1'
and AoDt Between :pFrmAoDt and :pToAoDt
And AoStatus <> 'C'
and paymenttermcd not in ('EXB-100','FREE-REPL')
and customercd<>'DC0230'
and b.compcode = c.compcode
and b.branchcd = c.branchcd
and b.seriescd = c.seriescd
and b.aono = c.aono
and b.slno = c.slno
and b.productcategory = c.productcategory
and b.producttype = c.producttype
and b.productsize = c.productsize
and b.actuate1 = c.actuate1
and b.actuate2 = c.actuate2
and b.stroke = c.stroke
and b.mount1 = c.mount1
and b.mount2 = c.mount2
and b.mount3 = c.mount3
and b.mount4 = c.mount4
and b.mount5 = c.mount5
and b.special = c.special
and NVL(deliverydt,'01-APR-01')>to_date('31-mar-01')
and c.deliveryqty-nvl(c.despatchqty,0)>0
and revised_shedule <= '28-feb-05'
and (basicprice*(c.deliveryqty-nvl(c.despatchqty,0)))>0
Group By b.compcode,customercd,b.aono,a.aodt,aotype, b.branchcd,b.seriescd,paymenttermcd,ldflag,porefno,porefdt,
b.slno,b.productcategory,
b.producttype,b.productsize,
b.actuate1,b.actuate2,b.stroke,
b.mount1,b.mount2,b.mount3,b.mount4,b.mount5,b.special, c.deliverydt, c.deliveryqty,c.revised_shedule
Best regards,
Raju.
|
] 
Blog
Search
Help
Members
Register
Login
Home
