중첩 INNER

작성일: 2024년 5월 20일

목표:

 

[Join문 중첩 사용]

*&---------------------------------------------------------------------*
*& Report ZTEST_TASK_JOIN
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT ZTEST_TASK_JOIN.


TABLES:     SCARR, spfli, sflight,sbook.

TYPE-POOLS: slis.                                 "ALV Declarations

*Data Declaration   "전역변수
*----------------
TYPES: BEGIN OF t_task_join,

  carrid TYPE SCARR-CARRID,
  CARRNAME TYPE SCARR-CARRNAME,
  CONNID TYPE SPFLI-CONNID,
  CITYFROM TYPE SPFLI-CITYFROM,
  CITYTO TYPE SPFLI-CITYTO,
  FLDATE TYPE sflight-fldate,
  bookid TYPE sbook-bookid,
  customid type sbook-customid,
  custtype type sbook-custtype,
  price type sflight-price,

 END OF t_task_JOIN.

DATA: it_task_join TYPE STANDARD TABLE OF t_task_join INITIAL SIZE 0,
      wa_task_join TYPE t_task_join.

*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 .


SELECTION-SCREEN BEGIN OF BLOCK part1 WITH FRAME TITLE text-009.
  SELECT-OPTIONS s_carrid FOR SCARR-carrid.
  SELECT-OPTIONS s_cityfr FOR SPFli-cityfrom.
  SELECT-OPTIONS s_cityto FOR SpfLI-cityto.


  "PARAMETERS p_carrid TYPE scarr-carrid OBLIGATORY DEFAULT 'AA'.
SELECTION-SCREEN END OF BLOCK part1.


INITIALIZATION.




************************************************************************
*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     =  3.
  "fieldcatalog-outputlen   = 10. "edit mode일때 보통 사용함
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CARRNAME'.
  fieldcatalog-seltext_m   = 'Airline name'.
  fieldcatalog-lzero = 'X'.
  fieldcatalog-col_pos     = 20.
  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     = 20.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CITYTO'.
  fieldcatalog-seltext_m   = 'Arrival city'.
  fieldcatalog-col_pos     = 20.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

    fieldcatalog-fieldname   = 'FLDATE'.
  fieldcatalog-seltext_m   = 'Flight date'.
  fieldcatalog-col_pos     = 8.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

 fieldcatalog-fieldname  ='bookid'.
 fieldcatalog-seltext_m = 'Booking number'.
 fieldcatalog-col_pos = 8.
 APPEND fieldcatalog TO fieldcatalog.
 CLEAR fieldcatalog.

 fieldcatalog-fieldname  ='customid'.
 fieldcatalog-seltext_m = 'customer number'.
 fieldcatalog-col_pos = 8.
 APPEND fieldcatalog TO fieldcatalog.
 CLEAR fieldcatalog.

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

 fieldcatalog-fieldname  ='price'.
 fieldcatalog-seltext_m = 'airdate'.
 fieldcatalog-col_pos = 15.
 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'. "colwidth_optimize를 일단 주고 outlen을 10으로 주는 것이 보통임.
  gd_layout-zebra = 'X'.
  gd_layout-totals_before_items = '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             = 'A'  "빈값 = 저장버튼 안나오게 X=디폴트세팅
    TABLES
      t_outtab           = it_task_join
    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, s~carrname, p~connid, p~cityfrom, p~cityto, f~fldate, f~bookid, f~customid, f~custtype,r~price
  from scarr as s
  left join spfli as p on p~carrid = s~carrid
  left join sbook as f on f~carrid = s~carrid
  left join sflight as r on r~carrid = s~carrid

  into table @it_task_join
  where s~carrid in @s_carrid
  AND p~cityfrom in @s_cityfr
  AND p~cityto in @s_cityto.

ENDFORM.                    " DATA_RETRIEVAL
*&---------------------------------------------------------------------*
*& Form DATA_COLLECT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM data_collect .

ENDFORM.