ABAP New open SQL:少し複雑なテーブル結合をしてみる

今回は、従来(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

以上