INNERJOINS :-
Inner join: - Inner join pick the data from both the tables if & only if there is one or more than one entry is available in the right hand side table with corresponding left hand side table.
Based on the given customer numbers display the customer numbers, customer names, sales document numbers, document date, item number, material number, material description, quantity, unit of measurement, net price by using inner join.
PROGRAM :
*&---------------------------------------------------------------------*
*& Report ZR_INNERJOIN
*&
*&---------------------------------------------------------------------*
REPORT ZR_INNERJOIN.
DATA : LV_KUNNR TYPE KNA1-KUNNR.
*selection screen
SELECTION-SCREEN BEGIN OF BLOCK A.
SELECT-OPTIONS SO_KUNNR FOR LV_KUNNR.
SELECTION-SCREEN END OF BLOCK A.
*types declaration
TYPES : BEGIN OF TY_FINAL,
KUNNR TYPE KNA1-KUNNR,
NAME1 TYPE KNA1-NAME1,
VBELN TYPE VBAK-VBELN,
AUDAT TYPE VBAK-AUDAT,
POSNR TYPE VBAP-POSNR,
MATNR TYPE VBAP-MATNR,
MAKTX TYPE MAKT-MAKTX,
END OF TY_FINAL.
*workarea and internal table declarations
DATA : WA_FINAL TYPE TY_FINAL,
IT_FINAL TYPE TABLE OF TY_FINAL.
*fetch data from database
SELECT KNA1~KUNNR
KNA1~NAME1
VBAK~VBELN
VBAK~AUDAT
VBAP~POSNR
VBAP~MATNR
MAKT~MAKTX
INTO TABLE IT_FINAL
FROM KNA1 INNER JOIN VBAK ON KNA1~KUNNR = VBAK~KUNNR
INNER JOIN VBAP ON VBAK~VBELN = VBAP~VBELN
INNER JOIN MAKT ON VBAP~MATNR = MAKT~MATNR
WHERE KNA1~KUNNR IN SO_KUNNR
AND MAKT~SPRAS = SY-LANGU.
*display final data
LOOP AT IT_FINAL INTO WA_FINAL.
WRITE :/ WA_FINAL-KUNNR,
WA_FINAL-NAME1,
WA_FINAL-VBELN,
WA_FINAL-AUDAT,
WA_FINAL-POSNR,
WA_FINAL-MATNR,
WA_FINAL-MAKTX.
ENDLOOP.
Inner join: - Inner join pick the data from both the tables if & only if there is one or more than one entry is available in the right hand side table with corresponding left hand side table.
Based on the given customer numbers display the customer numbers, customer names, sales document numbers, document date, item number, material number, material description, quantity, unit of measurement, net price by using inner join.
PROGRAM :
*&---------------------------------------------------------------------*
*& Report ZR_INNERJOIN
*&
*&---------------------------------------------------------------------*
REPORT ZR_INNERJOIN.
DATA : LV_KUNNR TYPE KNA1-KUNNR.
*selection screen
SELECTION-SCREEN BEGIN OF BLOCK A.
SELECT-OPTIONS SO_KUNNR FOR LV_KUNNR.
SELECTION-SCREEN END OF BLOCK A.
*types declaration
TYPES : BEGIN OF TY_FINAL,
KUNNR TYPE KNA1-KUNNR,
NAME1 TYPE KNA1-NAME1,
VBELN TYPE VBAK-VBELN,
AUDAT TYPE VBAK-AUDAT,
POSNR TYPE VBAP-POSNR,
MATNR TYPE VBAP-MATNR,
MAKTX TYPE MAKT-MAKTX,
END OF TY_FINAL.
*workarea and internal table declarations
DATA : WA_FINAL TYPE TY_FINAL,
IT_FINAL TYPE TABLE OF TY_FINAL.
*fetch data from database
SELECT KNA1~KUNNR
KNA1~NAME1
VBAK~VBELN
VBAK~AUDAT
VBAP~POSNR
VBAP~MATNR
MAKT~MAKTX
INTO TABLE IT_FINAL
FROM KNA1 INNER JOIN VBAK ON KNA1~KUNNR = VBAK~KUNNR
INNER JOIN VBAP ON VBAK~VBELN = VBAP~VBELN
INNER JOIN MAKT ON VBAP~MATNR = MAKT~MATNR
WHERE KNA1~KUNNR IN SO_KUNNR
AND MAKT~SPRAS = SY-LANGU.
*display final data
LOOP AT IT_FINAL INTO WA_FINAL.
WRITE :/ WA_FINAL-KUNNR,
WA_FINAL-NAME1,
WA_FINAL-VBELN,
WA_FINAL-AUDAT,
WA_FINAL-POSNR,
WA_FINAL-MATNR,
WA_FINAL-MAKTX.
ENDLOOP.