JOIN

REPORT ZIJOIN .
*GENERATING SALES REPORT FROM MORE THAN ONE TABLE ....
* KNA1 -CUSTOMER, VBAK-ORDER HEADER, VBAP-ORDER ITEM

TABLES : KNA1, VBAK.
DATA : BEGIN OF ITAB OCCURS 0,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
LAND1 LIKE KNA1-LAND1,
VBELN LIKE VBAK-VBELN,
VBTYP LIKE VBAK-VBTYP,
NETWR LIKE VBAK-NETWR,
END OF ITAB.

SELECT-OPTIONS: CUSTNO FOR KNA1-KUNNR.
SELECT * FROM KNA1 INTO CORRESPONDING FIELDS OF ITAB
WHERE KUNNR IN CUSTNO.
APPEND ITAB.
ENDSELECT.

SELECT * FROM VBAK INTO CORRESPONDING FIELDS OF ITAB.
APPEND ITAB.
ENDSELECT.

WRITE 35 'CUSTOMER WISE ORDER DETAILS REPORT'.
ULINE.

WRITE : / 'CUSTNO', 10 'CUST NAME', 35 'COUNTRY', 50 'ORDER NO',
65 'DOCTYPE', 75 'NETVALUE'.
ULINE.

LOOP AT ITAB.
WRITE : / ITAB-KUNNR, 10 ITAB-NAME1, 35 ITAB-LAND1, 50 ITAB-VBELN,
65 ITAB-VBTYP, 68 ITAB-NETWR.
ULINE.
AT END OF KUNNR.
SUM.
WRITE : 60 'TOTAL : ', 68 ITAB-NETWR.
NEW-PAGE.
ENDAT.
ENDLOOP.

**************** EXTRACTING DATA FROM TABLE TO STRUCTURE ***************
TABLES : KNA1.
DATA CUST LIKE KNA1.

SELECT * INTO CUST FROM KNA1 UP TO 4 ROWS.
WRITE: / CUST-KUNNR, CUST-NAME1, CUST-LAND1.
ENDSELECT.

************** INNER JOIN 1 *****************
TABLES : KNA1, VBAK.

DATA : BEGIN OF ITAB OCCURS 0,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
LAND1 LIKE KNA1-LAND1,
VBELN LIKE VBAK-VBELN,
VBTYP LIKE VBAK-VBTYP,
NETWR LIKE VBAK-NETWR,
END OF ITAB.

SELECT-OPTIONS : CUSTNO FOR KNA1-KUNNR.
SELECT K~KUNNR K~NAME1 K~LAND1 S~VBELN S~VBTYP S~NETWR
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM KNA1 AS K INNER JOIN VBAK AS S ON K~KUNNR = S~KUNNR WHERE
K~KUNNR IN CUSTNO.

LOOP AT ITAB.
WRITE : / ITAB-KUNNR, ITAB-NAME1, ITAB-LAND1, ITAB-VBELN, ITAB-VBTYP,
ITAB-NETWR.
ENDLOOP.

***************** INNER JOIN 2 ****************************
DATA: BEGIN OF WA,
CARRID TYPE SPFLI-CARRID,
CONNID TYPE SPFLI-CONNID,
FLDATE TYPE SFLIGHT-FLDATE,
BOOKID TYPE SBOOK-BOOKID,
END OF WA,
ITAB LIKE SORTED TABLE OF WA
WITH UNIQUE KEY CARRID CONNID FLDATE BOOKID.

SELECT P~CARRID P~CONNID F~FLDATE B~BOOKID
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM ( ( SPFLI AS P
INNER JOIN SFLIGHT AS F ON P~CARRID = F~CARRID AND
P~CONNID = F~CONNID )
INNER JOIN SBOOK AS B ON B~CARRID = F~CARRID AND
B~CONNID = F~CONNID AND
B~FLDATE = F~FLDATE ).
WHERE P~CITYFROM = 'FRANKFURT' AND
P~CITYTO = 'NEW YORK' AND
F~SEATSMAX > F~SEATSOCC.

LOOP AT ITAB INTO WA.
AT NEW FLDATE.
WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.
ENDAT.
WRITE / WA-BOOKID.
ENDLOOP.

************ OUTER JOIN *******************************
DATA: BEGIN OF WA,
CARRID TYPE SCARR-CARRID,
CARRNAME TYPE SCARR-CARRNAME,
CONNID TYPE SPFLI-CONNID,
END OF WA,
ITAB LIKE SORTED TABLE OF WA
WITH NON-UNIQUE KEY CARRID.

SELECT S~CARRID S~CARRNAME P~CONNID
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM SCARR AS S
LEFT OUTER JOIN SPFLI AS P ON S~CARRID = P~CARRID AND
P~CITYFROM = 'FRANKFURT'.

LOOP AT ITAB INTO WA.
WRITE: / WA-CARRID, WA-CARRNAME, WA-CONNID.
ENDLOOP.



Inner Join
REPORT ZJOIN_INER .
=================Check Error
TABLES : KNA1, VBAK.

Data : Begin of Customer occurs 0,
Cust_Code Like Kna1-MANDT,
Cust_Name Like KNA1-KUNNR,
Cust_Country Like Kna1-LAND1,
Cust_NAME1 Like Kna1-NAME1,
Doc_No Like Vbak-VBELN,
Doc_Type like Vbak-VBTYP,
END OF Customer.

SELECT-OPTIONS : CUSTNO FOR KNA1-KUNNR.

SELECT Cu~Cust_Code,Cu~Cust_Name,Cu~Cust_Country,
Do~VBELN,do~VBTYP,do~NETWR into CORRESPONDING field of Table Customer
From Kna1 as Cu Inner Join VBAK as Do on Cu~Kunnr = Do~Kunnr where
Cu~Kunnr in Custno FROM KNA1 AS K INNER JOIN VBAK AS S ON
K~KUNNR = S~KUNNR WHERE K~KUNNR IN CUSTNO.

=================End of Check Error
INNER JOIN
TABLES : KNA1, VBAK.
DATA : BEGIN OF ITAB OCCURS 0,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
LAND1 LIKE KNA1-LAND1,
VBELN LIKE VBAK-VBELN,
VBTYP LIKE VBAK-VBTYP,
NETWR LIKE VBAK-NETWR,
END OF ITAB.

SELECT-OPTIONS : CUSTNO FOR KNA1-KUNNR.

SELECT K~KUNNR K~NAME1 K~LAND1 S~VBELN S~VBTYP S~NETWR
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM KNA1 AS K INNER JOIN VBAK AS S ON K~KUNNR = S~KUNNR WHERE
K~KUNNR IN CUSTNO.

LOOP AT ITAB.
WRITE : / ITAB-KUNNR, ITAB-NAME1, ITAB-LAND1, ITAB-VBELN, ITAB-VBTYP,
ITAB-NETWR.
ENDLOOP.



OUTER JOIN
*TABLES SPFLI, scarr.

*DATA: BEGIN OF WA,
* CARRID TYPE SCARR-CARRID,
* CARRNAME TYPE SCARR-CARRNAME,
* CONNID TYPE SPFLI-CONNID,
* END OF WA,
*
* ITAB LIKE SORTED TABLE OF WA
* WITH NON-UNIQUE KEY CARRID.
*
*SELECT S~CARRID S~CARRNAME P~CONNID
*INTO CORRESPONDING FIELDS OF TABLE ITAB
*FROM SCARR AS S LEFT OUTER JOIN SPFLI AS P ON S~CARRID = P~CARRID
*AND P~CITYFROM = 'FRANKFURT'.
*
*LOOP AT ITAB INTO WA.
* WRITE: / WA-CARRID, WA-CARRNAME, WA-CONNID NO-ZERO.
*ENDLOOP.


Outer Join
REPORT ZJOIN_OUTER .

************ OUTER JOIN *******************************

TABLES SPFLI, scarr.

DATA: BEGIN OF WA,
CARRID TYPE SCARR-CARRID,
CARRNAME TYPE SCARR-CARRNAME,
CONNID TYPE SPFLI-CONNID,
END OF WA,

ITAB LIKE SORTED TABLE OF WA
WITH NON-UNIQUE KEY CARRID.

SELECT S~CARRID S~CARRNAME P~CONNID
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM SCARR AS S LEFT OUTER JOIN SPFLI AS P ON S~CARRID = P~CARRID
AND P~CITYFROM = 'FRANKFURT'.

LOOP AT ITAB INTO WA.
WRITE: / WA-CARRID, WA-CARRNAME, WA-CONNID NO-ZERO.
ENDLOOP.


TABLES : KNA1, VBAK.
DATA : BEGIN OF ITAB OCCURS 0,
KUNNR LIKE KNA1-KUNNR,
NAME1 LIKE KNA1-NAME1,
LAND1 LIKE KNA1-LAND1,
VBELN LIKE VBAK-VBELN,
VBTYP LIKE VBAK-VBTYP,
NETWR LIKE VBAK-NETWR,
END OF ITAB.

SELECT-OPTIONS : CUSTNO FOR KNA1-KUNNR.

SELECT K~KUNNR K~NAME1 K~LAND1 S~VBELN S~VBTYP S~NETWR
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM KNA1 AS K INNER JOIN VBAK AS S ON K~KUNNR = S~KUNNR WHERE
K~KUNNR IN CUSTNO.

LOOP AT ITAB.
WRITE : / ITAB-KUNNR, ITAB-NAME1, ITAB-LAND1, ITAB-VBELN, ITAB-VBTYP,
ITAB-NETWR.
ENDLOOP.



REPORT ZJOIN_OUTER .
TABLES SPFLI, scarr.

DATA: BEGIN OF WA,
CARRID TYPE SCARR-CARRID,
CARRNAME TYPE SCARR-CARRNAME,
CONNID TYPE SPFLI-CONNID,
END OF WA,
ITAB LIKE SORTED TABLE OF WA
WITH NON-UNIQUE KEY CARRID.
SELECT S~CARRID S~CARRNAME P~CONNID
INTO CORRESPONDING FIELDS OF TABLE ITAB
FROM SCARR AS S LEFT OUTER JOIN SPFLI AS P ON S~CARRID = P~CARRID
AND P~CITYFROM = 'FRANKFURT'.

LOOP AT ITAB INTO WA.
WRITE: / WA-CARRID, WA-CARRNAME, WA-CONNID NO-ZERO.
ENDLOOP.