🫡

6. ABAP Join문

ABAP Join문

SELECT FROM demo_join1 AS t1
         INNER JOIN demo_join2 AS t2 ON t2~d = t1~d
       FIELDS t1~a AS a1,
              t1~b AS b1,
              t1~c AS c1,
              t1~d AS d1,
              t2~d AS d2,
              t2~e AS e2,
              t2~f AS f2,
              t2~g AS g2,
              t2~h AS h2
       ORDER BY t1~d
       INTO CORRESPONDING FIELDS OF TABLE @itab.
itabi = itab.
ABAP
복사

ABAP에서 SQL JOIN 및 SELECT 문 사용하기

MS SQL에서는 SQL 쿼리에서 테이블의 별칭을 사용할 때 a.empno와 같은 형식을 사용한다.

하지만 ABAP에서는 a~empno 형식을 사용하여 테이블과 필드를 구분한다.

•

MS SQL: a.empno

•

ABAP: a~empno

이때, .(점) 대신 ~(물결)을 사용하는 것이 특징.

INTO CORRESPONDING FIELDS OF TABLE

INTO CORRESPONDING FIELDS OF TABLE을 사용할 때는 SELECT 절에서 선언한 필드의 순서와 구조의 순서가 다르더라도 같은 이름의 필드에 값을 자동으로 매칭하여 데이터를 넣어준다.

따라서 SELECT에서 필드 순서와 구조 필드 순서가 달라도 문제없이 데이터를 처리할 수 있다.

ABAP Join문 실습

구글에 ABAP SELECT JOIN example 치고 해당 첫번째 링크에 들어가서

3개 예시를 사용해준다.

*&---------------------------------------------------------------------*
*& Report Z6WEEK_ALV00_TEST_JOIN
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z6WEEK_ALV00_TEST_JOIN.



*&---------------------------------------------------------------------*
*& INNER JOIN
*&---------------------------------------------------------------------*

PARAMETERS: p_cityfr TYPE spfli-cityfrom,
            p_cityto TYPE spfli-cityto.

TYPES: BEGIN OF wa,
         fldate TYPE sflight-fldate,
         carrname TYPE scarr-carrname,
         connid   TYPE spfli-connid,
       END OF wa.

DATA itab TYPE SORTED TABLE OF wa
          WITH UNIQUE KEY fldate carrname connid.

SELECT c~carrname, p~connid, f~fldate
       FROM ( ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid
                              AND p~cityfrom = @p_cityfr
                              AND p~cityto   = @p_cityto )
         INNER JOIN sflight AS f ON f~carrid = p~carrid
                                AND f~connid = p~connid )
       INTO CORRESPONDING FIELDS OF TABLE @itab.

cl_demo_output=>display( itab ).




*&---------------------------------------------------------------------*
*& OUTER JOIN
*&---------------------------------------------------------------------*

*PARAMETERS p_cityfr TYPE spfli-cityfrom.
*
*TYPES: BEGIN OF wa,
*         carrid   TYPE scarr-carrid,
*         carrname TYPE scarr-carrname,
*         connid   TYPE spfli-connid,
*      END OF wa.
*DATA  itab TYPE SORTED TABLE OF wa
*                WITH NON-UNIQUE KEY carrid.
*
*SELECT s~carrid, s~carrname, p~connid
*       FROM scarr AS s
*       LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid
*                                  AND p~cityfrom = @p_cityfr
*       INTO CORRESPONDING FIELDS OF TABLE @itab.
*
*DELETE itab WHERE connid <> '0000'.
*
*cl_demo_output=>display( itab ).





*&---------------------------------------------------------------------*
*& CROSS JOIN
*&---------------------------------------------------------------------*


*DATA BEGIN OF wa.
*DATA mandt TYPE t000-mandt.
*DATA mtext TYPE t000-mtext.
*INCLUDE TYPE t100.
*DATA END OF wa.
*DATA itab LIKE STANDARD TABLE OF wa WITH EMPTY KEY.
*
*SELECT t000~mandt, t000~mtext, t100~*
*       FROM t000 CROSS JOIN t100
*       WHERE t100~arbgb = 'SABAPDEMOS'
*       ORDER BY t000~mandt, t100~sprsl, t100~msgnr
*       INTO TABLE @itab.
*
*cl_demo_output=>display( itab ).
ABAP
복사

첫번째 예시 소스를 수정

TYPES: BEGIN OF wa,
         carrid TYPE scarr-carrid,
         carrname TYPE scarr-carrname,
         connid TYPE spfli-connid,
         cityfrom TYPE spfli-cityfrom,
         cityto TYPE spfli-cityto,
         fldate TYPE sflight-fldate,
       END OF wa.

DATA itab TYPE TABLE OF wa.

SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto
       FROM ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid )
       INTO CORRESPONDING FIELDS OF TABLE @itab.

cl_demo_output=>display( itab ).
ABAP
복사

서로 결합하는 테이블 데이터를 보자.

scarr 테이블

spfli 테이블

결과

cl_demo_output=>display( itab ).

이 구문은 ABAP에서 테이블 내용을 팝업 형태로 출력하는 구문이다.

여기서 =>는 클래스가 메서드를 직접 실행하는 것을 의미한다.

->는 객체를 받아서 메서드를 실행하는 구조를 나타냄.

중첩 INNER JOIN

TYPES: BEGIN OF wa,
         carrid TYPE scarr-carrid,
         carrname TYPE scarr-carrname,
         connid TYPE spfli-connid,
         cityfrom TYPE spfli-cityfrom,
         cityto TYPE spfli-cityto,
         fldate TYPE sflight-fldate,
       END OF wa.

DATA itab TYPE TABLE OF wa.

SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto
  ,  f~fldate
       FROM ( ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid )
         INNER JOIN sflight AS f ON f~carrid = p~carrid
                                AND f~connid = p~connid )
       INTO CORRESPONDING FIELDS OF TABLE @itab.

cl_demo_output=>display( itab ).
ABAP
복사

같은 필드(겹치는)가 여러 개면 고유한 값을 주기 위해 그 필드들을 조건에 걸어준다.

새로운 OPEN SQL 구문

ABAP의 새로운 OPEN SQL 구문에서는 SELECT 문에서 콤마(,)를 사용할 수 있으며, 이때 변수 앞에 @를 붙여야 함.

이는 조회화면의 변수와 DATA로 선언한 변수를 구분하기 위함이다.

SELECT carrid, connid
  INTO TABLE @itab
  FROM spfli.
ABAP
복사

구버전에서는 콤마를 사용할 수 없으며, 지원되는 SQL 문법만 사용할 수 있다.두번째 예시 소스를 수정

두번째 예시 소스를 수정

*&---------------------------------------------------------------------*
*& OUTER JOIN
*&---------------------------------------------------------------------*

TYPES: BEGIN OF wa,
         carrid TYPE scarr-carrid,
         carrname TYPE scarr-carrname,
         connid TYPE spfli-connid,
         cityfrom TYPE spfli-cityfrom,
         cityto TYPE spfli-cityto,
       END OF wa.

DATA itab TYPE TABLE OF wa.

SELECT s~carrid, s~carrname, p~connid, p~cityfrom, p~cityto
       FROM scarr AS s
       LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid
       INTO CORRESPONDING FIELDS OF TABLE @itab.

*DELETE itab WHERE connid <> '0000'.

cl_demo_output=>display( itab ).
ABAP
복사

자재별 매출내역 JOIN 예시

자재 테이블

자재코드	자재텍스트
11	컴퓨터
22	마우스
33	키보드
44	스피커

매출내역 테이블

자재코드	매출금액
11	100
22	300

매출내역이 있는것만 부를거면 → INNER JOIN

자재를 다 보여주고 해당되는 건 다 붙여서 보여줄고면 → LEFT OUTER JOIN / LOOP MOVE READ

INNER JOIN (이너조인)

매출 내역이 있는 자재만 조회하는 경우, 즉 매출 데이터가 있는 자재만 가져오려면 이너조인을 사용.

SELECT a~matnr, a~maktx, b~netwr
  FROM mara AS a
  INNER JOIN vbap AS b
  ON a~matnr = b~matnr
  INTO TABLE @itab.
ABAP
복사

LEFT OUTER JOIN (레프트 아우터 조인)

모든 자재를 조회하고, 매출 내역이 있는 경우만 매출 데이터를 붙여서 가져오는 경우는 레프트 아우터 조인을 사용한다.

SELECT a~matnr, a~maktx, b~netwr
  FROM mara AS a
  LEFT JOIN vbap AS b
  ON a~matnr = b~matnr
  INTO TABLE @itab.
ABAP
복사

NUMC 필드와 빈 값 처리

NUMC 필드는 빈 값이 있을 경우 자동으로 0000과 같이 0으로 채워진다.

조건문을 작성할 때, 빈 값 처리가 필요할 수 있다.

세번째 예시 소스

DATA BEGIN OF wa.                    " 작업 영역 구조 시작
DATA mandt TYPE t000-mandt.           " 클라이언트 필드 (T000 테이블의 mandt 필드)
DATA mtext TYPE t000-mtext.           " 클라이언트 텍스트 필드 (T000 테이블의 mtext 필드)
INCLUDE TYPE t100.                    " T100 테이블의 모든 필드를 포함 (T100 테이블의 모든 필드가 wa 구조에 추가됨)
DATA END OF wa.                       " 작업 영역 구조 끝

DATA itab LIKE STANDARD TABLE OF wa WITH EMPTY KEY.  " wa 구조를 기반으로 하는 내부 테이블 itab 선언

SELECT t000~mandt, t000~mtext, t100~*          " T000 테이블의 mandt, mtext 필드와 T100 테이블의 모든 필드 선택
  FROM t000 CROSS JOIN t100                    " T000과 T100 테이블 간의 CROSS JOIN 수행
  WHERE t100~arbgb = 'SABAPDEMOS'              " T100 테이블의 arbgb 필드가 'SABAPDEMOS'인 레코드만 필터링
  ORDER BY t000~mandt, t100~sprsl, t100~msgnr  " 클라이언트(mandt), 언어 코드(sprsl), 메시지 번호(msgnr)로 정렬
  INTO TABLE @itab.                            " 조회된 결과를 itab 내부 테이블에 저장

cl_demo_output=>display( itab ).       " 조회된 itab 테이블 내용을 팝업 형태로 화면에 출력
ABAP
복사

t000 테이블 데이터

t100 테이블의 arbgb 필드가 'SABAPDEMOS'인 레코드 갯수

t100 테이블 데이터

총 28개 출력

다양한 JOIN 예시

•

1. SCARR와 SPFLI의 INNER JOIN

◦

SCARR 테이블의 carrid와 SPFLI 테이블의 carrid를 기준으로 INNER JOIN을 사용하여 데이터를 조회.

•

2. SCARR와 SPFLI의 LEFT OUTER JOIN

◦

모든 항공사를 조회하고, 해당 항공사에 연결된 비행 정보를 추가하는 경우 LEFT OUTER JOIN을 사용.

•

3. RIGHT OUTER JOIN과 CROSS JOIN

◦

RIGHT OUTER JOIN과 CROSS JOIN의 경우 SELECT 구문에서 반드시 콤마를 사용해야 하며, INTO 구문은 마지막에 위치해야 한다.

*&---------------------------------------------------------------------*
*& Report Z6WEEK_ALV00_TEST_JOIN
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z6WEEK_ALV00_TEST_JOIN2.

TABLES:     scarr, spfli, sflight.

TYPE-POOLS: slis.                                 "ALV Declarations

*Data Declaration
*----------------
TYPES: BEGIN OF t_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,
 END OF t_join.

DATA: it_join TYPE STANDARD TABLE OF t_join ,
      wa_join TYPE t_join.

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


SELECTION-SCREEN BEGIN OF BLOCK part1 WITH FRAME TITLE text-001.
SELECT-OPTIONS s_carrid FOR scarr-carrid.
SELECTION-SCREEN END OF BLOCK part1.
ABAP
복사

여기다가 이제 만들면 된다.

FORM data_select .
  SELECT carrid carrname connid cityfrom cityto
    FROM scarr
    INTO TABLE it_join
    WHERE carrid IN s_carrid.
  
 
ENDFORM.
ABAP
복사

큰틀부터 잡아서

FORM data_select .
  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto
    FROM scarr AS c
    INNER JOIN spfli AS p
    ON c~carrid = p~carrid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.
  
 
ENDFORM.
ABAP
복사

전체 수정된 소스

*&---------------------------------------------------------------------*
*& Report Z6WEEK_ALV00_TEST_JOIN
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z6WEEK_ALV00_TEST_JOIN2.

TABLES:     scarr, spfli, sflight.

TYPE-POOLS: slis.                                 "ALV Declarations

*Data Declaration
*----------------
TYPES: BEGIN OF t_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,
 END OF t_join.

DATA: it_join TYPE STANDARD TABLE OF t_join ,
      wa_join TYPE t_join.

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


SELECTION-SCREEN BEGIN OF BLOCK part1 WITH FRAME TITLE text-001.
SELECT-OPTIONS s_carrid FOR scarr-carrid.
SELECTION-SCREEN END OF BLOCK part1.


************************************************************************
*Start-of-selection.
START-OF-SELECTION.

  PERFORM data_select.

*  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   = 'CARRID'.
  fieldcatalog-col_pos     = 0.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CARRNAME'.
  fieldcatalog-seltext_m   = 'CARRNAME'.
  fieldcatalog-col_pos     = 1.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CONNID'.
  fieldcatalog-seltext_m   = 'CONNID'.
  fieldcatalog-col_pos     = 2.
  fieldcatalog-lzero       = 'X'.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CITYFROM'.
  fieldcatalog-seltext_m   = 'CITYFROM'.
  fieldcatalog-col_pos     = 3.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CITYTO'.
  fieldcatalog-seltext_l   = 'CITYTO'.
  fieldcatalog-col_pos     = 4.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

    fieldcatalog-fieldname   = 'FLDATE'.
  fieldcatalog-seltext_l   = 'FLDATE'.
  fieldcatalog-col_pos     = 5.
  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'
*      i_save             = 'A'
*      i_save             = 'U'
    TABLES
      t_outtab           = it_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.
*  DATA: ld_color(1) TYPE c.
*
*  SELECT mandt carrid carrname currcode url
*   UP TO NUM ROWS
*    FROM scarr
*    INTO TABLE it_scarr
*    WHERE carrid IN s_carrid
**    WHERE carrid = p_carrid.
*    AND carrname IN s_cname
*    AND currcode IN s_code
*    AND url IN s_url.
*
*ENDFORM.                    " DATA_RETRIEVAL






*&---------------------------------------------------------------------*
*& Form DATA_SELECT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM data_select .
  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto
    FROM scarr AS c
    INNER JOIN spfli AS p
    ON c~carrid = p~carrid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.


ENDFORM.
ABAP
복사

만약 중첩 조인으로 한다면

*&---------------------------------------------------------------------*
*& Report Z6WEEK_ALV00_TEST_JOIN3
*&---------------------------------------------------------------------*
*&
*&---------------------------------------------------------------------*
REPORT Z6WEEK_ALV00_TEST_JOIN3.

TABLES:     scarr, spfli, sflight.

TYPE-POOLS: slis.                                 "ALV Declarations

*Data Declaration
*----------------
TYPES: BEGIN OF t_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,
 END OF t_join.

DATA: it_join TYPE STANDARD TABLE OF t_join ,
      wa_join TYPE t_join.

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


SELECTION-SCREEN BEGIN OF BLOCK part1 WITH FRAME TITLE text-001.
SELECT-OPTIONS s_carrid FOR scarr-carrid.
SELECTION-SCREEN END OF BLOCK part1.


************************************************************************
*Start-of-selection.
START-OF-SELECTION.

  PERFORM data_select.

  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   = 'CARRID'.
  fieldcatalog-col_pos     = 0.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CARRNAME'.
  fieldcatalog-seltext_m   = 'CARRNAME'.
  fieldcatalog-col_pos     = 1.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CONNID'.
  fieldcatalog-seltext_m   = 'CONNID'.
  fieldcatalog-col_pos     = 2.
  fieldcatalog-lzero       = 'X'.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CITYFROM'.
  fieldcatalog-seltext_m   = 'CITYFROM'.
  fieldcatalog-col_pos     = 3.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'CITYTO'.
  fieldcatalog-seltext_l   = 'CITYTO'.
  fieldcatalog-col_pos     = 4.
  APPEND fieldcatalog TO fieldcatalog.
  CLEAR  fieldcatalog.

  fieldcatalog-fieldname   = 'FLDATE'.
  fieldcatalog-seltext_l   = 'FLDATE'.
  fieldcatalog-col_pos     = 5.
  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'.

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
    EXCEPTIONS
      program_error      = 1
      OTHERS             = 2.
  IF sy-subrc <> 0.

  ENDIF.


ENDFORM.                    " DISPLAY_ALV_REPORT




*&---------------------------------------------------------------------*
*& Form DATA_SELECT
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& -->  p1        text
*& <--  p2        text
*&---------------------------------------------------------------------*
FORM data_select .
  SELECT c~carrid c~carrname 
         p~connid p~cityfrom p~cityto 
         f~fldate
    FROM scarr AS c
    INNER JOIN spfli   AS p ON c~carrid = p~carrid
    INNER JOIN sflight AS f ON p~carrid = f~carrid
                            AND p~connid = f~connid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.


ENDFORM.
ABAP
복사

94개 노출

LEFT OUTER JOIN으로 수정해서 진행하면

FORM data_select .
*  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto
*    FROM scarr AS c
*    INNER JOIN spfli AS p
*    ON c~carrid = p~carrid
*    INTO TABLE it_join
*    WHERE c~carrid IN s_carrid.

  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto
    FROM scarr AS c
    LEFT OUTER JOIN spfli AS p
    ON c~carrid = p~carrid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.


ENDFORM.
ABAP
복사

RIGHT OUTER JOIN으로 수정해서 진행하면

  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto
    FROM scarr AS c
    RIGHT OUTER JOIN spfli AS p
    ON c~carrid = p~carrid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.
ABAP
복사

활성화 시키면 이제 에러가 난다…

콤마가 들어가줘야 한다.

근데 콤마넣어주고 실행시켜줘도 다른 오류가 난다.

@를 이용해달라 한다.

  SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto
    FROM scarr AS c
    RIGHT OUTER JOIN spfli AS p
    ON c~carrid = p~carrid
    INTO TABLE @it_join
    WHERE c~carrid IN @s_carrid.
ABAP
복사

CROSS JOIN으로 수정해서 진행하면

  SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto
    FROM scarr AS c
    CROSS JOIN spfli AS p
    INTO TABLE @it_join
    WHERE c~carrid IN @s_carrid.
ABAP
복사

INTO구문이 반드시 끝으로 가야한다고 한다.

  SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto
    FROM scarr AS c
    CROSS JOIN spfli AS p
    WHERE c~carrid IN @s_carrid
    INTO TABLE @it_join.
ABAP
복사

252개

252개 알고싶으면 디버깅찍어서

중첩 INNER JOIN으로 수정해서 진행하면

  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto f~fldate
    FROM scarr AS c
    INNER JOIN spfli AS p
    ON c~carrid = p~carrid
    INNER JOIN sflight AS f
    ON p~carrid = f~carrid
    AND p~connid = f~connid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.
ABAP
복사

94개

다양한 중첩 JOIN으로 수정해서 진행하면

  SELECT c~carrid c~carrname p~connid p~cityfrom p~cityto f~fldate
    FROM scarr AS c
    LEFT OUTER JOIN spfli AS p
    ON c~carrid = p~carrid
    INNER JOIN sflight AS f
    ON p~carrid = f~carrid
    AND p~connid = f~connid
    INTO TABLE it_join
    WHERE c~carrid IN s_carrid.
ABAP
복사

  SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto, f~fldate
    FROM scarr AS c
    LEFT OUTER JOIN spfli AS p
    ON c~carrid = p~carrid
    LEFT OUTER JOIN sflight AS f
    ON p~carrid = f~carrid
    AND p~connid = f~connid
    INTO TABLE @it_join
    WHERE c~carrid IN @s_carrid.
ABAP
복사

상단에 sbook추가하고

  SELECT c~carrid, c~carrname, p~connid, p~cityfrom, p~cityto, f~fldate, b~bookid
    FROM scarr AS c
    LEFT OUTER JOIN spfli AS p
    ON c~carrid = p~carrid
    LEFT OUTER JOIN sflight AS f
    ON p~carrid = f~carrid
    AND p~connid = f~connid
    LEFT OUTER JOIN sbook AS b
    ON f~carrid = b~carrid
    AND f~connid = b~connid
    AND f~fldate = b~fldate
    INTO TABLE @it_join
    WHERE c~carrid IN @s_carrid.
ABAP
복사

참고 사항

•

LEFT OUTER JOIN과 INNER JOIN에서는 콤마(,)를 사용하지 않아도 된다.

•

RIGHT OUTER JOIN과 CROSS JOIN에서는 반드시 콤마(,)를 사용해야 하며, SELECT 구문에서 INTO 구문은 마지막에 위치해야 한다.