|
Posted by 皮皮熊 on 2008-05-26 04:44 下午
|
|
达人:
请教一下
目标:将数据表中的结果存入到内表A中,
同时也对查询出来的明细汇总也存入到内表A中,然后一起输出到EXCEL模板中.
说明:如下这段代码
我将数据表中的数据查询出来存到内表中ITAB_OK
同时要对查询出来的结果根据A~AUART "销售凭证类型
B~MATNR "物料号
B~POSNR "销售凭证项目
对 B~KWMENG "订单数
C~MENGE "数量
汇总也存入ITAB_OK中然后然后一起输出到EXCEL模板中
请问如何做.
另多个表关联我采用了连续INNER JOIN 听说这种写法性能性不高.
请问有什么更好的解决方法吗?
谢谢
SELECT
A~AUART "销售凭证类型
B~ERDAT "建立纪录日期
B~VSTEL "接收点
B~LGORT "库存地点
B~KWMENG "订单数
B~MATNR "物料号
B~POSNR "销售凭证项目
B~VBELN "销售凭证
A~KUNNR "售达方
A~VSBED "装运条件
A~VKBUR "销售办事处
A~VKGRP "销售组
A~SPART "产品组
A~VTWEG "分销渠道
A~VKORG "销售组织
A~AUGRU "订购原因
A~XBLNR "参考凭证号
C~DMBTR "按本位币计的金额
C~MENGE "数量
C~BWART "移动类型
C~MBLNR "物料凭证编号
D~BUDAT "凭证中的记帐日期
INTO CORRESPONDING FIELDS OF TABLE ITAB_OK
FROM VBAK AS A INNER JOIN VBAP AS B ON A~VBELN = B~VBELN
INNER JOIN MSEG AS C ON B~MATNR = C~MATNR AND B~VBELN = C~KDAUF
AND B~POSNR = C~KDPOS
INNER JOIN MKPF AS D ON C~MBLNR = D~MBLNR AND C~MJAHR = D~MJAHR
WHERE A~AUART = 'ZFD' AND A~KUNNR IN P_KUNNR AND A~VKORG IN
P_VKORG AND A~VKBUR IN P_VKBUR AND A~VTWEG IN P_VTWEG AND B~MATNR IN
P_MATNR AND B~ERDAT IN P_ERDAT AND A~ERNAM IN P_ERNAM AND D~BUDAT IN
P_BUDAT AND B~VBELN IN P_VBELN AND C~BWART = '903'
|
|
|
|
Posted by 香蕉 on 2008-05-27 09:12 上午
|
|
大哥.你这代码能跑得出来吗?这么多个表JOIN,数据量大了之后你一点执行,你就可以去打场球再回来看结果了
|
|
|
|
Posted by 皮皮熊 on 2008-05-27 11:31 上午
|
就是啊
如何优化它啊
|
|
|
|
Posted by 香蕉 on 2008-05-27 03:01 下午
|
|
一般JOIN不要超过2张表,而且数据量大的表不要JOIN,比如MSEG这个表.
|
|
|
|
Posted by 香蕉 on 2008-05-27 05:05 下午
|
data: begin of itab_ok OCCURS 0,
AUART like VBAK-auart,"销售凭证类型
ERDAT like VBAP-erdat,"建立纪录日期
VSTEL like VBAP-vstel,"接收点
LGORT like VBAP-lgort,"库存地点
KWMENG like VBAP-kwmeng,"订单数
MATNR like vbap-matnr,"物料号
POSNR like VBAP-posnr,"销售凭证项目
VBELN like VBAP-vbeln,"销售凭证
KUNNR like VBAK-kunnr,"售达方
VSBED like VBAK-vsbed,"装运条件
VKBUR like VBAK-vkbur,"销售办事处
VKGRP like VBAK-vkgrp,"销售组
SPART like VBAK-spart,"产品组
VTWEG like VBAK-vtweg,"分销渠道
VKORG like VBAK-vkorg,"销售组织
AUGRU like VBAK-augru,"订购原因
XBLNR like VBAK-xblnr,"参考凭证号
DMBTR like MSEG-dmbtr,"按本位币计的金额
MENGE like MSEG-menge, "数量
BWART like MSEG-bwart,"移动类型
MBLNR like mseg-mblnr,"物料凭证编号
budat like mkpf-budat,
mjahr like mseg-mjahr,
end of itab_ok.
select A~AUART "销售凭证类型
B~ERDAT "建立纪录日期
B~VSTEL "接收点
B~LGORT "库存地点
B~KWMENG "订单数
B~MATNR "物料号
B~POSNR "销售凭证项目
B~VBELN "销售凭证
A~KUNNR "售达方
A~VSBED "装运条件
A~VKBUR "销售办事处
A~VKGRP "销售组
A~SPART "产品组
A~VTWEG "分销渠道
A~VKORG "销售组织
A~AUGRU "订购原因
A~XBLNR "参考凭证号
INTO CORRESPONDING FIELDS OF TABLE ITAB_OK
FROM VBAK AS A INNER JOIN VBAP AS B ON A~VBELN = B~VBELN
WHERE A~AUART = 'ZFD'
AND A~KUNNR IN P_KUNNR
AND A~VKORG IN P_VKORG
AND A~VKBUR IN P_VKBUR
AND A~VTWEG IN P_VTWEG
AND B~MATNR IN P_MATNR
AND B~ERDAT IN P_ERDAT
AND A~ERNAM IN P_ERNAM
* AND D~BUDAT IN P_BUDAT
AND B~VBELN IN P_VBELN.
* AND C~BWART = '903'.
loop at itab_ok.
select single DMBTR "按本位币计的金额
MENGE "数量
BWART "移动类型
MBLNR "物料凭证编号
mjahr
into (itab_ok-dmbtr,itab_ok-menge,itab_ok-bwart,itab_ok-mblnr,itab_ok-mjahr)
from mseg
where matnr = itab_ok-matnr
and kdauf = itab_ok-vbeln
and kdpos = itab_ok-posnr
and bwart = '903'.
select single BUDAT
into itab_ok-budat
from mkpf
WHERE mblnr = itab_ok-mblnr
and mjahr = itab_ok-mjahr
AND BUDAT IN P_BUDAT.
modify itab_ok.
endloop.
改成这样可能会好一点.试试吧.
|
|
|
|
Posted by testoop on 2008-05-29 11:02 上午
|
在SAP里面编程有时不如SQL Server方便的，他的SQL 链接不怎么方便。我们需要写更多的代码来实现一个SQL语句实现的功能。
楼上兄弟的做法是一种很好的思路。
|
|
|
|
Posted by 皮皮熊 on 2008-05-29 11:19 上午
|
 香蕉 wrote: | data: begin of itab_ok OCCURS 0,
AUART like VBAK-auart,"销售凭证类型
ERDAT like VBAP-erdat,"建立纪录日期
VSTEL like VBAP-vstel,"接收点
LGORT like VBAP-lgort,"库存地点
KWMENG like VBAP-kwmeng,"订单数
MATNR like vbap-matnr,"物料号
POSNR like VBAP-posnr,"销售凭证项目
VBELN like VBAP-vbeln,"销售凭证
KUNNR like VBAK-kunnr,"售达方
VSBED like VBAK-vsbed,"装运条件
VKBUR like VBAK-vkbur,"销售办事处
VKGRP like VBAK-vkgrp,"销售组
SPART like VBAK-spart,"产品组
VTWEG like VBAK-vtweg,"分销渠道
VKORG like VBAK-vkorg,"销售组织
AUGRU like VBAK-augru,"订购原因
XBLNR like VBAK-xblnr,"参考凭证号
DMBTR like MSEG-dmbtr,"按本位币计的金额
MENGE like MSEG-menge, "数量
BWART like MSEG-bwart,"移动类型
MBLNR like mseg-mblnr,"物料凭证编号
budat like mkpf-budat,
mjahr like mseg-mjahr,
end of itab_ok.
select A~AUART "销售凭证类型
B~ERDAT "建立纪录日期
B~VSTEL "接收点
B~LGORT "库存地点
B~KWMENG "订单数
B~MATNR "物料号
B~POSNR "销售凭证项目
B~VBELN "销售凭证
A~KUNNR "售达方
A~VSBED "装运条件
A~VKBUR "销售办事处
A~VKGRP "销售组
A~SPART "产品组
A~VTWEG "分销渠道
A~VKORG "销售组织
A~AUGRU "订购原因
A~XBLNR "参考凭证号
INTO CORRESPONDING FIELDS OF TABLE ITAB_OK
FROM VBAK AS A INNER JOIN VBAP AS B ON A~VBELN = B~VBELN
WHERE A~AUART = 'ZFD'
AND A~KUNNR IN P_KUNNR
AND A~VKORG IN P_VKORG
AND A~VKBUR IN P_VKBUR
AND A~VTWEG IN P_VTWEG
AND B~MATNR IN P_MATNR
AND B~ERDAT IN P_ERDAT
AND A~ERNAM IN P_ERNAM
* AND D~BUDAT IN P_BUDAT
AND B~VBELN IN P_VBELN.
* AND C~BWART = '903'.
loop at itab_ok.
select single DMBTR "按本位币计的金额
MENGE "数量
BWART "移动类型
MBLNR "物料凭证编号
mjahr
into (itab_ok-dmbtr,itab_ok-menge,itab_ok-bwart,itab_ok-mblnr,itab_ok-mjahr)
from mseg
where matnr = itab_ok-matnr
and kdauf = itab_ok-vbeln
and kdpos = itab_ok-posnr
and bwart = '903'.
select single BUDAT
into itab_ok-budat
from mkpf
WHERE mblnr = itab_ok-mblnr
and mjahr = itab_ok-mjahr
AND BUDAT IN P_BUDAT.
modify itab_ok.
endloop.
改成这样可能会好一点.试试吧. |
|
谢谢
|
|