select b.fitemno,sum(a.ftrnqty) from ( SELECT 'PRODUCTION' ,to_char(a.ftrndte,'yymm') , b.fitemno , b.fitemln , a.ftrnqty , b.fitemcls2 ,b.fstdprice/fexchgrate ,b.fstdprice/fexchgrate*a.ftrnqty ,to_char(a.ftrnno) ,a.ffromwhcd ,' ' ,' ' ,' ' ,b.fitemcls3 ,'PRODUCT' FROM TRNSCTF a, ITEMM b,RATEM c WHERE (a.ftrn1 in ('PRC') ) AND (a.ftrn2='RCT') AND (a.fitemno=b.fitemno) AND (b.fitemtyp in ('S','M')) and c.fcurr='USD' and a.ffromwhcd <> 'MZ-RY' and to_char(a.ftrndte,'yymm')>='0604' and b.fitemno not in (select fitemno from OMZ_PART) union all SELECT 'PRODUCTION' ,to_char(a.ftrndte,'yymm') , b.fitemno , b.fitemln , a.ftrnqty , b.fitemcls2 ,b.fstdprice/fexchgrate ,b.fstdprice/fexchgrate*a.ftrnqty ,to_char(a.ftrnno) ,a.ffromwhcd ,' ' ,' ' ,' ' ,b.fitemcls3 ,'PRODUCT' FROM TRNSCTF a, ITEMM b,RATEM c WHERE (a.ftrn1 in ('MRC') ) AND (a.ftrn2='RCT') AND (a.fitemno=b.fitemno) AND (b.fitemtyp in ('S','M')) and c.fcurr='USD' and to_char(a.ftrndte,'yymm')>='0604' and b.fitemno not in (select fitemno from OMZ_PART) union all SELECT 'PRODUCTION' ,to_char(a.ftrndte,'yymm') , b.fitemno , b.fitemln , a.ftrnqty , b.fitemcls2 ,b.fstdprice/fexchgrate ,b.fstdprice/fexchgrate*a.ftrnqty ,to_char(a.ftrnno) ,a.ffromwhcd ,' ' ,' ' ,' ' ,b.fitemcls3 ,'PART' FROM TRNSCTF a, ITEMM b,RATEM c WHERE (a.ftrn1 in ('PRC') ) AND (a.ftrn2='RCT') AND (a.fitemno=b.fitemno) AND (b.fitemtyp in ('S','M')) and c.fcurr='USD' and to_char(a.ftrndte,'yymm')>='0604' and b.fitemno in (select fitemno from OMZ_PART) union all SELECT 'PRODUCTION' ,to_char(a.ftrndte,'yymm') , b.fitemno , b.fitemln , a.ftrnqty , b.fitemcls2 ,b.fstdprice/fexchgrate ,b.fstdprice/fexchgrate*a.ftrnqty ,to_char(a.ftrnno) ,a.ffromwhcd ,' ' ,' ' ,' ' ,b.fitemcls3 ,'PART' FROM TRNSCTF a, ITEMM b,RATEM c WHERE (a.ftrn1 in ('MRC') ) AND (a.ftrn2='RCT') AND (a.fitemno=b.fitemno) AND (b.fitemtyp in ('S','M')) and c.fcurr='USD' and to_char(a.ftrndte,'yymm')>='0604' and b.fitemno in (select fitemno from OMZ_PART) ) group by .............................; 是这个意思吗?
(
SELECT 'PRODUCTION'
,to_char(a.ftrndte,'yymm')
, b.fitemno
, b.fitemln
, a.ftrnqty
, b.fitemcls2
,b.fstdprice/fexchgrate
,b.fstdprice/fexchgrate*a.ftrnqty
,to_char(a.ftrnno)
,a.ffromwhcd
,' '
,' '
,' '
,b.fitemcls3
,'PRODUCT'
FROM TRNSCTF a, ITEMM b,RATEM c
WHERE (a.ftrn1 in ('PRC') )
AND (a.ftrn2='RCT')
AND (a.fitemno=b.fitemno)
AND (b.fitemtyp in ('S','M'))
and c.fcurr='USD'
and a.ffromwhcd <> 'MZ-RY'
and to_char(a.ftrndte,'yymm')>='0604'
and b.fitemno not in (select fitemno from OMZ_PART)
union all
SELECT 'PRODUCTION'
,to_char(a.ftrndte,'yymm')
, b.fitemno
, b.fitemln
, a.ftrnqty
, b.fitemcls2
,b.fstdprice/fexchgrate
,b.fstdprice/fexchgrate*a.ftrnqty
,to_char(a.ftrnno)
,a.ffromwhcd
,' '
,' '
,' '
,b.fitemcls3
,'PRODUCT'
FROM TRNSCTF a, ITEMM b,RATEM c
WHERE (a.ftrn1 in ('MRC') )
AND (a.ftrn2='RCT')
AND (a.fitemno=b.fitemno)
AND (b.fitemtyp in ('S','M'))
and c.fcurr='USD'
and to_char(a.ftrndte,'yymm')>='0604'
and b.fitemno not in (select fitemno from OMZ_PART)
union all
SELECT 'PRODUCTION'
,to_char(a.ftrndte,'yymm')
, b.fitemno
, b.fitemln
, a.ftrnqty
, b.fitemcls2
,b.fstdprice/fexchgrate
,b.fstdprice/fexchgrate*a.ftrnqty
,to_char(a.ftrnno)
,a.ffromwhcd
,' '
,' '
,' '
,b.fitemcls3
,'PART'
FROM TRNSCTF a, ITEMM b,RATEM c
WHERE (a.ftrn1 in ('PRC') )
AND (a.ftrn2='RCT')
AND (a.fitemno=b.fitemno)
AND (b.fitemtyp in ('S','M'))
and c.fcurr='USD'
and to_char(a.ftrndte,'yymm')>='0604'
and b.fitemno in (select fitemno from OMZ_PART)
union all
SELECT 'PRODUCTION'
,to_char(a.ftrndte,'yymm')
, b.fitemno
, b.fitemln
, a.ftrnqty
, b.fitemcls2
,b.fstdprice/fexchgrate
,b.fstdprice/fexchgrate*a.ftrnqty
,to_char(a.ftrnno)
,a.ffromwhcd
,' '
,' '
,' '
,b.fitemcls3
,'PART'
FROM TRNSCTF a, ITEMM b,RATEM c
WHERE (a.ftrn1 in ('MRC') )
AND (a.ftrn2='RCT')
AND (a.fitemno=b.fitemno)
AND (b.fitemtyp in ('S','M'))
and c.fcurr='USD'
and to_char(a.ftrndte,'yymm')>='0604'
and b.fitemno in (select fitemno from OMZ_PART)
)
group by .............................;
是这个意思吗?