ABAP 코딩 (7) Open SQL-Join

🌞 Inner join / Outer join

[SYNC] ABAP Dictionary (5) - Database View / Maintenance View

Dictionary 에서 View 를 생성할 때 Join 관계를 설정하듯 Program 에서도 join 을 걸어
N개의 테이블에 있는 값을 Select 할 수 있다.

INNER JOIN : AND 조건
LEFT OUTER JOIN : OR 조건

---

SELECT [Alias~테이블]

INTO CORRESPONDING FIELDS OF TABLE [테이블]

FROM [헤더테이블] AS (Alias)

( ⭐ Open SQL 에서 이런식으로 Join 관계를 설정할 수 있다. )
1. INNER JOIN [아이템 테이블] AS (Alias)
2. LEFT OUTER JOIN [테이블] AS (Alias)

ON [Alias~필드] = [Alias~필드]`

ORDER BY [Alias~필드] : 정렬이 될 필드 ( 보통 PK 들로 정렬 )

⭐ ON : JOIN 할 필드들 작성

💡 [ Inner join 실습 ]

[ 문제 ]

[ 코드 ]

DATA : BEGIN OF gs_airprice,
         carrid     TYPE spfli-carrid,
         connid     TYPE spfli-connid,
         fldate     TYPE sflight-fldate,
         cityfrom   TYPE spfli-cityfrom,
         cityto     TYPE spfli-cityto,
         price      TYPE sflight-price,
         paymentsum TYPE sflight-paymentsum,
         currency   TYPE sflight-currency,
       END OF gs_airprice,

       gt_airprice LIKE TABLE OF gs_airprice.

CLEAR   : gs_airprice.
REFRESH : gt_airprice.

SELECT a~carrid a~connid b~fldate a~cityfrom a~cityto
       b~price b~paymentsum b~currency
  INTO CORRESPONDING FIELDS OF TABLE gt_airprice
  FROM spfli AS a
 INNER JOIN sflight AS b
    ON a~carrid = b~carrid
   AND a~connid = b~connid
 WHERE a~carrid IN ('DL', 'LH')
 ORDER BY a~carrid a~connid b~fldate.

❗ 조인 관계를 설정할 때 반드시 PK 확인하여 설정한다.

💡 [ Outer join 실습 ]

[ 문제 ]

[ 코드 ]

DATA : BEGIN OF gs_mat3,
         matnr TYPE mara-matnr,
         werks TYPE marc-werks,
         mtart TYPE mara-mtart,
         mtbez TYPE t134t-mtbez,
         matkl TYPE mara-matkl,
         wgbez TYPE t023t-wgbez,
         mbrsh TYPE mara-mbrsh,
         mbbez TYPE t137t-mbbez,
       END OF gs_mat3,

       gt_mat3 LIKE TABLE OF gs_mat3.

CLEAR   : gs_mat3.
REFRESH : gt_mat3.

SELECT a~matnr a~mtart a~matkl a~mbrsh
       b~werks
       c~mtbez
       d~wgbez
       e~mbbez
  INTO CORRESPONDING FIELDS OF TABLE gt_mat3
  FROM mara AS a
 INNER JOIN marc AS b
    ON a~matnr = b~matnr
  LEFT OUTER JOIN t134t AS c
    ON a~mtart = c~mtart
   AND c~spras = sy-langu
  LEFT OUTER JOIN t023t AS d
    ON a~matkl = d~matkl
   AND d~spras = sy-langu
  LEFT OUTER JOIN t137t AS e
    ON a~mbrsh = e~mbrsh
   AND e~spras = sy-langu
 WHERE a~matkl = '01'
   AND b~werks = '1010'.

IF sy-subrc <> 0.
  WRITE 'Data no found'.
  STOP.
ENDIF.

cl_demo_output=>display( gt_mat3 ).

❗ Text Table 은 상황에 따라 다국어가 지원이 안될 수 있어 OUTER JOIN 을 사용한다.
❗ SY-LANGU 로그인한 언어를 저장하는 변수로 언어키에 조건을 걸어준다.