少し間が空きました。
今回は、受注伝票の取得を例にしてみました。
受注伝票関連のテーブルを、明細単位でブッコヌキしてみます。
考え方としては、VBAP：明細テーブルを軸にして以下テーブル結合します。
・VBKD：納入日程行
・VBAK：ヘッダ
・VBKD：ビジネスデータ
・VBPA：取引先機能
L084: SELECT
L085: ti0~vbeln,
L086: ti0~posnr,
L087: ti0~matnr,
L088: ti0~kwmeng,
L089: MAX( td0~edatu ) AS edatu,
L090: SUM( td0~bmeng ) AS bmeng,
L091: ti2~kunnr AS kunnr_i,
L092: th2~kunnr AS kunnr_h,
L093: CASE
L094: WHEN ti2~kunnr IS NOT NULL THEN ti2~kunnr
L095: ELSE th2~kunnr END AS kunnr,
L096: COUNT(*) AS count
L097: FROM vbap AS ti0
L098: LEFT OUTER JOIN vbep AS td0
L099: ON td0~vbeln = ti0~vbeln
L100: AND td0~posnr = ti0~posnr
L101: LEFT OUTER JOIN vbkd AS ti1
L102: ON ti1~vbeln = ti0~vbeln
L103: AND ti1~posnr = ti0~posnr
L104: LEFT OUTER JOIN vbpa AS ti2
L105: ON ti2~vbeln = ti0~vbeln
L106: AND ti2~posnr = ti0~posnr
L107: AND ti2~parvw = 'WE'
L108: INNER JOIN vbak AS th0
L109: ON th0~vbeln = ti0~vbeln
L110: LEFT OUTER JOIN vbkd AS th1
L111: ON th1~vbeln = ti0~vbeln
L112: AND th1~posnr = '000000'
L113: LEFT OUTER JOIN vbpa AS th2
L114: ON th2~vbeln = ti0~vbeln
L115: AND th2~posnr = '000000'
L116: AND th2~parvw = 'WE'
L117: GROUP BY
L118: ti0~vbeln,
L119: ti0~posnr,
L120: ti0~matnr,
L121: ti0~kwmeng,
L122: ti2~kunnr,
L123: th2~kunnr
L124: ORDER BY
L125: ti0~vbeln,
L126: ti0~posnr
L127: INTO TABLE @DATA(lt_data).
ポイントは3点
VBKD：ビジネスデータおよびVBPA：取引先機能については、
ヘッダレコードと明細レコードが混在しているので、
それぞれについて2回テーブル結合しています。
L101、L110、L104、L113
明細データを優先するため、
CASE文を使用して明細データの有無(NULL値の確認)を確認して、
存在したら明細データを出力し、存在しなかったらヘッダデータを出力するようにしています。
ビジネスデータについては今回未出力としていますが、同様の記述が可能です。
L093〜L095
納入日程行の確認数量の合計値を取得するために、SUM関数を使用しています。
納入日付については、MAX関数を用いて最大値を取得しています。
L089、L090
少しプログラムを改変しました。(20171018)
L001:*&---------------------------------------------------------------------*
L002:*& Report Y_SQL_CONSOLE
L003:*&---------------------------------------------------------------------*
L004:*&
L005:*&---------------------------------------------------------------------*
L006:REPORT y_sql_console.
L007:
L008:*-----------------------------------------------------------------------
L009:* グローバル変数
L010:*-----------------------------------------------------------------------
L011:DATA gv_subrc TYPE sy-subrc.
L012:
L013:*-----------------------------------------------------------------------
L014:* 選択画面
L015:*-----------------------------------------------------------------------
L016:DATA gv_char1 TYPE char10.
L017:TYPES gtrt_char1 LIKE RANGE OF gv_char1.
L018:SELECT-OPTIONS s_char1 FOR gv_char1.
L019:
L020:DATA gv_char2 TYPE char10.
L021:TYPES gtrt_char2 LIKE RANGE OF gv_char2.
L022:SELECT-OPTIONS s_char2 FOR gv_char2.
L023:
L024:DATA gv_numc1 TYPE numc08.
L025:TYPES gtrt_numc1 LIKE RANGE OF gv_numc1.
L026:SELECT-OPTIONS s_numc1 FOR gv_numc1.
L027:
L028:DATA gv_numc2 TYPE numc08.
L029:TYPES gtrt_numc2 LIKE RANGE OF gv_numc2.
L030:SELECT-OPTIONS s_numc2 FOR gv_numc2.
L031:
L032:DATA gv_dats1 TYPE dats.
L033:TYPES gtrt_dats1 LIKE RANGE OF gv_dats1.
L034:SELECT-OPTIONS s_dats1 FOR gv_dats1.
L035:
L036:DATA gv_dats2 TYPE dats.
L037:TYPES gtrt_dats2 LIKE RANGE OF gv_dats2.
L038:SELECT-OPTIONS s_dats2 FOR gv_dats2.
L039:
L040:DATA gv_tims1 TYPE tims.
L041:TYPES gtrt_tims1 LIKE RANGE OF gv_tims1.
L042:SELECT-OPTIONS s_tims1 FOR gv_tims1.
L043:
L044:DATA gv_tims2 TYPE tims.
L045:TYPES gtrt_tims2 LIKE RANGE OF gv_tims2.
L046:SELECT-OPTIONS s_tims2 FOR gv_tims2.
L047:
L048:*-----------------------------------------------------------------------
L049:* 主処理
L050:*-----------------------------------------------------------------------
L051:START-OF-SELECTION.
L052: PERFORM start_of_selection
L053: USING
L054: s_char1
L055: s_char2
L056: s_numc1
L057: s_numc2
L058: s_dats1
L059: s_dats2
L060: s_tims1
L061: s_tims2
L062: CHANGING
L063: gv_subrc.
L064:
L065:*-----------------------------------------------------------------------
L066:* サブルーチン
L067:*-----------------------------------------------------------------------
L068:FORM start_of_selection
L069: USING
L070: urt_char1 TYPE gtrt_char1
L071: urt_char2 TYPE gtrt_char2
L072: urt_numc1 TYPE gtrt_numc1
L073: urt_numc2 TYPE gtrt_numc2
L074: urt_dats1 TYPE gtrt_dats1
L075: urt_dats2 TYPE gtrt_dats2
L076: urt_tims1 TYPE gtrt_tims1
L077: urt_tims2 TYPE gtrt_tims2
L078: CHANGING
L079: cv_subrc TYPE sy-subrc.
L080:*-----------------------------------------------------------------------
L081:* ここに、SQL文を書いてね☆
L082:* 格納する内部テーブルは、lt_dataにしてね☆
L083:* lt_dataをインライン定義すると、楽ちんだ☆
L084: SELECT
L085: ti0~vbeln,
L086: ti0~posnr,
L087: ti0~matnr,
L088: ti0~kwmeng,
L089: MAX( td0~edatu ) AS edatu,
L090: SUM( td0~bmeng ) AS bmeng,
L091: ti2~kunnr AS kunnr_i,
L092: th2~kunnr AS kunnr_h,
L093: CASE
L094: WHEN ti2~kunnr IS NOT NULL THEN ti2~kunnr
L095: ELSE th2~kunnr END AS kunnr,
L096: COUNT(*) AS count
L097: FROM vbap AS ti0
L098: LEFT OUTER JOIN vbep AS td0
L099: ON td0~vbeln = ti0~vbeln
L100: AND td0~posnr = ti0~posnr
L101: LEFT OUTER JOIN vbkd AS ti1
L102: ON ti1~vbeln = ti0~vbeln
L103: AND ti1~posnr = ti0~posnr
L104: LEFT OUTER JOIN vbpa AS ti2
L105: ON ti2~vbeln = ti0~vbeln
L106: AND ti2~posnr = ti0~posnr
L107: AND ti2~parvw = 'WE'
L108: INNER JOIN vbak AS th0
L109: ON th0~vbeln = ti0~vbeln
L110: LEFT OUTER JOIN vbkd AS th1
L111: ON th1~vbeln = ti0~vbeln
L112: AND th1~posnr = '000000'
L113: LEFT OUTER JOIN vbpa AS th2
L114: ON th2~vbeln = ti0~vbeln
L115: AND th2~posnr = '000000'
L116: AND th2~parvw = 'WE'
L117: GROUP BY
L118: ti0~vbeln,
L119: ti0~posnr,
L120: ti0~matnr,
L121: ti0~kwmeng,
L122: ti2~kunnr,
L123: th2~kunnr
L124: ORDER BY
L125: ti0~vbeln,
L126: ti0~posnr
L127: INTO TABLE @DATA(lt_data).
L128:*-----------------------------------------------------------------------
L129: TRY.
L130: cl_salv_table=>factory(
L131: IMPORTING
L132: r_salv_table = DATA(lo_alv)
L133: CHANGING
L134: t_table = lt_data ).
L135: CATCH cx_salv_msg INTO DATA(lx_salv_msg).
L136: cv_subrc = 8.
L137: RETURN.
L138: ENDTRY.
L139: DATA(lo_functions) = lo_alv->get_functions( ).
L140: lo_functions->set_all( ).
L141: DATA(lo_selections) = lo_alv->get_selections( ).
L142: lo_selections->set_selection_mode(
L143: EXPORTING
L144: value = if_salv_c_selection_mode=>multiple ).
L145: lo_alv->display( ).
L146:
L147: cv_subrc = 0.
L148:ENDFORM.
検索キーワード
ABAP
ABAP 7.4
ABAP 7.40
ABAP 7.5
ABAP 7.50
SQL
Open SQL
New Open SQL
Classic Open SQL
SAP HANA
Code to Data
Code Pushdown
INNER JOIN
LEFT OUTER JOIN
外部結合
自己結合
IS NULL
CASE文
GROUP BY
集合関数
SUM()
MAX()
以上
