今回は、従来(ABAP 7.3以前)では有効化エラーとなってしまうけれど、
New open SQLだから書ける!という類のテーブル結合を書いてみます。
BSEGテーブルに、勘定科目の名称を付加する。
SELECT t0~bukrs, t0~belnr, t0~gjahr, t0~buzei, t0~shkzg, t0~dmbtr, t2~waers AS waers_d, t0~wrbtr, t1~waers, t0~hkont, t3~txt20 FROM bseg AS t0 INNER JOIN bkpf AS t1 ON t1~bukrs = t0~bukrs AND t1~belnr = t0~belnr AND t1~gjahr = t0~gjahr INNER JOIN t001 AS t2 ON t2~bukrs = t0~bukrs INNER JOIN skat AS t3 "注目 ON t3~ktopl = t2~ktopl "注目 AND t3~saknr = t0~hkont "注目 AND t3~spras = @sy-langu ORDER BY t0~bukrs, t0~belnr, t0~gjahr, t0~buzei INTO TABLE @DATA(lt_data).
注目すべきは、
SKAT:勘定コード表: テキストとT001:会社コードの結合条件と
SKAT:勘定コード表: テキストとBSEG:会計伝票明細の結合条件
つまり、3つのテーブルにまたがる結合条件を記述しているのです。
これを、従来からある(Classic) Open SQLで書くと有効化時にエラーが発生します。
少しプログラムを改変しました。(20171018)
*&---------------------------------------------------------------------* *& Report Y_SQL_CONSOLE *&---------------------------------------------------------------------* *& *&---------------------------------------------------------------------* REPORT y_sql_console. *----------------------------------------------------------------------- * グローバル変数 *----------------------------------------------------------------------- DATA gv_subrc TYPE sy-subrc. *----------------------------------------------------------------------- * 選択画面 *----------------------------------------------------------------------- DATA gv_char1 TYPE char10. TYPES gtrt_char1 LIKE RANGE OF gv_char1. SELECT-OPTIONS s_char1 FOR gv_char1. DATA gv_char2 TYPE char10. TYPES gtrt_char2 LIKE RANGE OF gv_char2. SELECT-OPTIONS s_char2 FOR gv_char2. DATA gv_numc1 TYPE numc08. TYPES gtrt_numc1 LIKE RANGE OF gv_numc1. SELECT-OPTIONS s_numc1 FOR gv_numc1. DATA gv_numc2 TYPE numc08. TYPES gtrt_numc2 LIKE RANGE OF gv_numc2. SELECT-OPTIONS s_numc2 FOR gv_numc2. DATA gv_dats1 TYPE dats. TYPES gtrt_dats1 LIKE RANGE OF gv_dats1. SELECT-OPTIONS s_dats1 FOR gv_dats1. DATA gv_dats2 TYPE dats. TYPES gtrt_dats2 LIKE RANGE OF gv_dats2. SELECT-OPTIONS s_dats2 FOR gv_dats2. DATA gv_tims1 TYPE tims. TYPES gtrt_tims1 LIKE RANGE OF gv_tims1. SELECT-OPTIONS s_tims1 FOR gv_tims1. DATA gv_tims2 TYPE tims. TYPES gtrt_tims2 LIKE RANGE OF gv_tims2. SELECT-OPTIONS s_tims2 FOR gv_tims2. *----------------------------------------------------------------------- * 主処理 *----------------------------------------------------------------------- START-OF-SELECTION. PERFORM start_of_selection USING s_char1 s_char2 s_numc1 s_numc2 s_dats1 s_dats2 s_tims1 s_tims2 CHANGING gv_subrc. *----------------------------------------------------------------------- * サブルーチン *----------------------------------------------------------------------- FORM start_of_selection USING urt_char1 TYPE gtrt_char1 urt_char2 TYPE gtrt_char2 urt_numc1 TYPE gtrt_numc1 urt_numc2 TYPE gtrt_numc2 urt_dats1 TYPE gtrt_dats1 urt_dats2 TYPE gtrt_dats2 urt_tims1 TYPE gtrt_tims1 urt_tims2 TYPE gtrt_tims2 CHANGING cv_subrc TYPE sy-subrc. *----------------------------------------------------------------------- * ここに、SQL文を書いてね☆ * 格納する内部テーブルは、lt_dataにしてね☆ * lt_dataをインライン定義すると、楽ちんだ☆ SELECT t0~bukrs, t0~belnr, t0~gjahr, t0~buzei, t0~shkzg, t0~dmbtr, t2~waers AS waers_d, t0~wrbtr, t1~waers, t0~hkont, t3~txt20 FROM bseg AS t0 INNER JOIN bkpf AS t1 ON t1~bukrs = t0~bukrs AND t1~belnr = t0~belnr AND t1~gjahr = t0~gjahr INNER JOIN t001 AS t2 ON t2~bukrs = t0~bukrs INNER JOIN skat AS t3 "注目 ON t3~ktopl = t2~ktopl "注目 AND t3~saknr = t0~hkont "注目 AND t3~spras = @sy-langu ORDER BY t0~bukrs, t0~belnr, t0~gjahr, t0~buzei INTO TABLE @DATA(lt_data). *----------------------------------------------------------------------- TRY. cl_salv_table=>factory( IMPORTING r_salv_table = DATA(lo_alv) CHANGING t_table = lt_data ). CATCH cx_salv_msg INTO DATA(lx_salv_msg). cv_subrc = 8. RETURN. ENDTRY. DATA(lo_functions) = lo_alv->get_functions( ). lo_functions->set_all( ). DATA(lo_selections) = lo_alv->get_selections( ). lo_selections->set_selection_mode( EXPORTING value = if_salv_c_selection_mode=>multiple ). lo_alv->display( ). cv_subrc = 0. ENDFORM.
検索キーワード
ABAP
ABAP 7.4
ABAP 7.40
ABAP 7.5
ABAP 7.50
SQL
Open SQL
New Open SQL
SAP HANA
Code to Data
Code Pushdown
INNER JOIN
以上