중첩 Join시 유의 사항

최초 작성일: 2024-05-29

목적: Join문 부시기

 

 

 

*&---------------------------------------------------------------------*
*& Report ZALV_TRIPLE_JOIN
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZALV_TRIPLE_JOIN.

TABLES:      SCARR, spfli, sflight,sbook.

TYPE-POOLS: slis.                                 "ALV Declarations

*Data Declaration
*----------------
TYPES: BEGIN OF t_join_double,
  carrid TYPE scarr-carrid,
  connid TYPE spfli-connid,
  cityfrom TYPE spfli-cityfrom,
  cityto TYPE spfli-cityto,
  fltime TYPE spfli-fltime,
  fldate TYPE sflight-fldate,
  custtype TYPE sbook-custtype,
  smoker TYPE sbook-smoker,
  luggweight TYPE sbook-luggweight,
  wunit type sbook-wunit,
 END OF t_join_double.

DATA: it_join_double TYPE STANDARD TABLE OF t_join_double INITIAL SIZE 0,
      wa_join_double TYPE t_join_double.

*ALV data declarations
DATA: fieldcatalog TYPE slis_t_fieldcat_alv WITH HEADER LINE,
      gd_tab_group TYPE slis_t_sp_group_alv,
      gd_layout    TYPE slis_layout_alv,
      gd_repid     LIKE sy-repid.


DATA : t TYPE slis_t_sp_group_alv .
************************************************************************
*Start-of-selection.
START-OF-SELECTION.

  PERFORM data_retrieval.
  PERFORM build_fieldcatalog.
  PERFORM build_layout.
  PERFORM display_alv_report.


*&---------------------------------------------------------------------*
*&      Form  BUILD_FIELDCATALOG
*&---------------------------------------------------------------------*
*       Build Fieldcatalog for ALV Report
*----------------------------------------------------------------------*
FORM build_fieldcatalog.

  fieldcatalog-fieldname   = 'carrid'.
  fieldcatalog-seltext_m   = 'Airline code'.
  fieldcatalog-col_pos     = 1.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'connid'.
  fieldcatalog-seltext_m   = 'flight connection number'.
  fieldcatalog-col_pos     = 2.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'cityfrom'.
  fieldcatalog-seltext_m   = 'departure city'.
  fieldcatalog-col_pos     = 3.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'cityto'.
  fieldcatalog-seltext_m   = 'arrical city'.
  fieldcatalog-col_pos     = 4.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'fltime'.
  fieldcatalog-seltext_m   = 'flight time'.
  fieldcatalog-col_pos     = 5.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'fldate'.
  fieldcatalog-seltext_m   = 'flight date'.
  fieldcatalog-col_pos     = 6.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'custtype'.
  fieldcatalog-seltext_m   = 'customer type'.
  fieldcatalog-col_pos     = 7.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'smoker'.
  fieldcatalog-seltext_m   = 'smoker'.
  fieldcatalog-col_pos     = 8.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'luggweight'.
  fieldcatalog-seltext_m   = 'weight of luggage'.
  fieldcatalog-col_pos     = 9.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

    fieldcatalog-fieldname   = 'wunit'.
  fieldcatalog-seltext_m   = 'weight unit'.
  fieldcatalog-col_pos     = 10.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

ENDFORM.                    " BUILD_FIELDCATALOG


*&---------------------------------------------------------------------*
*&      Form  BUILD_LAYOUT
*&---------------------------------------------------------------------*
*       Build layout for ALV grid report
*----------------------------------------------------------------------*
FORM build_layout.

  gd_layout-no_input          = 'X'.
  gd_layout-colwidth_optimize = 'X'.
  gd_layout-zebra = 'X'.
*  gd_layout-info_fieldname =      'LINE_COLOR'.
*  gd_layout-def_status = 'A'.

ENDFORM.                    " BUILD_LAYOUT


*&---------------------------------------------------------------------*
*&      Form  DISPLAY_ALV_REPORT
*&---------------------------------------------------------------------*
*       Display report using ALV grid
*----------------------------------------------------------------------*
FORM display_alv_report.
  gd_repid = sy-repid.
  CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
    EXPORTING
      i_callback_program = gd_repid
      is_layout          = gd_layout
      it_fieldcat        = fieldcatalog[]
      i_save             = 'X'
    TABLES
      t_outtab           = it_join_double
    EXCEPTIONS
      program_error      = 1
      OTHERS             = 2.
  IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.


ENDFORM.                    " DISPLAY_ALV_REPORT


*&---------------------------------------------------------------------*
*&      Form  DATA_RETRIEVAL
*&---------------------------------------------------------------------*
*       Retrieve data form EKPO table and populate itab it_ekko
*----------------------------------------------------------------------*
FORM data_retrieval.

SELECT S~CARRID,P~CONNID,P~CITYFROM,P~CITYTO,P~FLTIME,F~FLDATE,B~CUSTTYPE,
  B~SMOKER,B~LUGGWEIGHT,B~WUNIT
  FROM SCARR AS S
  LEFT OUTER JOIN SPFLI AS P ON P~CARRID = S~CARRID
  LEFT OUTER JOIN SFLIGHT AS F ON
   F~CARRID = P~CARRID
  AND  F~CONNID = P~CONNID
  LEFT OUTER JOIN SBOOK AS B ON
  B~CARRID = F~CARRID
  AND
  B~CONNID = F~CONNID
  AND
  B~FLDATE = F~FLDATE
  INTO TABLE @it_join_double.

ENDFORM.                    " DATA_RETRIEVAL

 

 

테이블 조인 시, Unique한 값을 불러오기 위해서 매핑 되는 키 값을 최대한 가져올것

(특히 해당 필드 값이 원 테이블의 키 값 인 경우에는 가져와야함)