新版本ABAP已支持从一个内表中select ,或者join 一个内表。
仅允许使用一次,不允许同时join两个内表,或者from内表又join内表
项目中问题:
SELECT的表中有来自五个表的SELECT-OPTION字段,超过了数据库建议连接查询的三个表,此时原先的做法是先查询三个带SELECT-OPTION字段的内表,然后用for all entries in主表带出剩余的两个SELECT-OPTION字段,最后loop的时候对主表赋值。
此时如果对主表赋值不加以限制,那么最后主表当中后两条被SELECT-OPTION限制的字段是正常的,但是没有被限制的字段就会为空,这样是不满足需要的结果,如下图演示所示
此时有两种解决方法可以使用。
第一种:在loop主表时做判断,如果所限制的数据不在select-option中,就跳过此次循环,不将该行数据添加到最终ALV显示的表中,但是此时会循环多余的数据,造成时间浪费
REPORT ZDEMO_SEELE.
TYPES: BEGIN OF ty_data,
order_id LIKE ZSO_10_H-ORDER_ID,
cpn_name LIKE ZSO_10_H-cpn_name,
order_item LIKE ZSO_10_I-ORDER_ITEM,
prd_name LIKE ZSO_10_i-prd_name,
END OF ty_data.
DATA:gw_data TYPE ty_data,
gt_data LIKE TABLE OF gw_data.
RANGES s_item FOR ZSO_10_I-ORDER_ITEM.
DATA gs_item LIKE LINE OF s_item.
gs_item-sign = 'I'.
gs_item-option = 'EQ'.
gs_item-low = '海盐1'.
APPEND gs_item TO s_item.
gs_item-sign = 'I'.
gs_item-option = 'EQ'.
gs_item-low = '蛋糕3'.
APPEND gs_item TO s_item.
SELECT
ZSO_10_H~ORDER_ID,
ZSO_10_H~cpn_name,
ZSO_10_I~ORDER_ITEM
FROM ZSO_10_H
LEFT JOIN ZSO_10_I ON ZSO_10_I~ORDER_ID = ZSO_10_H~ORDER_ID
WHERE
ZSO_10_H~ORDER_ID = '10000003'
INTO TABLE @DATA(lt_data_A).
SELECT
ZSO_10_I~ORDER_ID,
ZSO_10_I~prd_name,
ZSO_10_I~ORDER_ITEM
FROM ZSO_10_I
FOR ALL ENTRIES IN @lt_data_A
WHERE
ZSO_10_I~ORDER_ID = @lt_data_A-ORDER_ID AND
ZSO_10_I~prd_name IN @s_item
INTO TABLE @DATA(lt_data_B).
SORT lt_data_A BY order_item.
* 这里会将主表全部循环,造成时间浪费
LOOP AT lt_data_A INTO DATA(lw_data_A).
MOVE-CORRESPONDING lw_data_A TO gw_data.
READ TABLE lt_data_B INTO DATA(lw_data_B) WITH KEY order_item = lw_data_A-order_item BINARY SEARCH.
IF sy-subrc EQ 0.
MOVE-CORRESPONDING lw_data_B TO gw_data.
ENDIF.
* 判断是否符合SELECT-OPTION 条件
IF gw_data-prd_name NOT IN s_item.
CONTINUE.
ENDIF.
APPEND gw_data TO gt_data.
CLEAR gw_data.
ENDLOOP.
第二种:SELECT查询时将数据库从表和内表主表进行连接查询
REPORT ZDEMO_SEELE.
TYPES: BEGIN OF ty_data,
order_id LIKE ZSO_10_H-ORDER_ID,
cpn_name LIKE ZSO_10_H-cpn_name,
order_item LIKE ZSO_10_I-ORDER_ITEM,
prd_name LIKE ZSO_10_i-prd_name,
END OF ty_data.
DATA:gw_data TYPE ty_data,
gt_data LIKE TABLE OF gw_data.
RANGES s_item FOR ZSO_10_I-ORDER_ITEM.
DATA gs_item LIKE LINE OF s_item.
gs_item-sign = 'I'.
gs_item-option = 'EQ'.
gs_item-low = '海盐1'.
APPEND gs_item TO s_item.
gs_item-sign = 'I'.
gs_item-option = 'EQ'.
gs_item-low = '蛋糕3'.
APPEND gs_item TO s_item.
SELECT
ZSO_10_H~ORDER_ID,
ZSO_10_H~cpn_name,
ZSO_10_I~ORDER_ITEM
FROM ZSO_10_H
LEFT JOIN ZSO_10_I ON ZSO_10_I~ORDER_ID = ZSO_10_H~ORDER_ID
WHERE
ZSO_10_H~ORDER_ID = '10000003'
INTO TABLE @DATA(lt_data_A).
* 此时内表和数据库表连接查询的数据就是我们需要的数据
SELECT
A~ORDER_ITEM,
A~cpn_name,
A~ORDER_ID,
B~prd_name
FROM @lt_data_A AS A
LEFT JOIN ZSO_10_I AS B ON A~ORDER_ITEM = B~ORDER_ITEM
WHERE
B~prd_name IN @s_item
INTO TABLE @DATA(lt_data_B).