04-09-2008 12:56 AM
We know the ABAP4 syntax for inner join, but never tried outer join and we even don't know if there is any outer join syntax in ABAP4 or not. Would be very appreciated if some ABAP expert here let us know if there is any outer join and list the difference between inner join and outer join with detailed senarios or when we would use outer join or when to use inner join? Also give a sample code on the outer join if there is any in ABAP?
Thanks and we will give you reward points!
04-09-2008 1:21 AM
Hye..
Left outer join between table 1 and table 2 where column D in both tables set the join condition:
Table 1 Table 2
A | B | C | D | D | E | F | G | H |
a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |
a2 | b2 | c2 | 1 | 3 | e2 | f2 | g2 | h2 |
a3 | b3 | c3 | 2 | 4 | e3 | f3 | g3 | h3 |
a4 | b4 | c4 | 3 |
|--|||--|
\ /
\ /
\ /
\ /
\/
Left Outer Join
|--||||||||--|
| A | B | C | D | D | E | F | G | H |
|--||||||||--|
| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |
| a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 |
| a3 | b3 | c3 | 2 |NULL|NULL|NULL|NULL|NULL|
| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 |
|--||||||||--|
Example
Output a list of all custimers with their bookings for October 15th, 2001:
DATA: CUSTOMER TYPE SCUSTOM,
BOOKING TYPE SBOOK.
SELECT SCUSTOMNAME SCUSTOMPOSTCODE SCUSTOM~CITY
SBOOKFLDATE SBOOKCARRID SBOOKCONNID SBOOKBOOKID
INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
BOOKING-BOOKID)
FROM SCUSTOM LEFT OUTER JOIN SBOOK
ON SCUSTOMID = SBOOKCUSTOMID AND
SBOOK~FLDATE = '20011015'
ORDER BY SCUSTOMNAME SBOOKFLDATE.
WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
BOOKING-BOOKID.
ENDSELECT.
Reward if helpful.
Thanks.
Imran.
04-09-2008 1:15 AM
Hi Kevin,
Yes there is OUTER JOIN syntax in ABAP. Just do F1 on keyword OUTER JOIN you will get sample code.
Check below SAP help
http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ec77446011d189700000e8322d00/frameset.htm
http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/frameset.htm
Regards,
Atish
04-09-2008 1:21 AM
Hye..
Left outer join between table 1 and table 2 where column D in both tables set the join condition:
Table 1 Table 2
A | B | C | D | D | E | F | G | H |
a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |
a2 | b2 | c2 | 1 | 3 | e2 | f2 | g2 | h2 |
a3 | b3 | c3 | 2 | 4 | e3 | f3 | g3 | h3 |
a4 | b4 | c4 | 3 |
|--|||--|
\ /
\ /
\ /
\ /
\/
Left Outer Join
|--||||||||--|
| A | B | C | D | D | E | F | G | H |
|--||||||||--|
| a1 | b1 | c1 | 1 | 1 | e1 | f1 | g1 | h1 |
| a2 | b2 | c2 | 1 | 1 | e1 | f1 | g1 | h1 |
| a3 | b3 | c3 | 2 |NULL|NULL|NULL|NULL|NULL|
| a4 | b4 | c4 | 3 | 3 | e2 | f2 | g2 | h2 |
|--||||||||--|
Example
Output a list of all custimers with their bookings for October 15th, 2001:
DATA: CUSTOMER TYPE SCUSTOM,
BOOKING TYPE SBOOK.
SELECT SCUSTOMNAME SCUSTOMPOSTCODE SCUSTOM~CITY
SBOOKFLDATE SBOOKCARRID SBOOKCONNID SBOOKBOOKID
INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
BOOKING-BOOKID)
FROM SCUSTOM LEFT OUTER JOIN SBOOK
ON SCUSTOMID = SBOOKCUSTOMID AND
SBOOK~FLDATE = '20011015'
ORDER BY SCUSTOMNAME SBOOKFLDATE.
WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
BOOKING-BOOKID.
ENDSELECT.
Reward if helpful.
Thanks.
Imran.