02-13-2012 4:31 PM
Dear experts,
I would like to find all the rows of table "A" that are not stored in table "B".
In Oracle I would use the Left Outer Join specifying "B.whtaeverfield is null" in the WHERE clause.
Unfortunately it seems that this is not possibile in ABAP ("No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE contintion ...).
Have you ever faced this situation? How did you manage it (without splitting the query into two sub-queries)?
Thanks a lot.
Alex
02-13-2012 5:20 PM
Look at subquery technique (see tips & tricks button in transaction SE30...SQL Interface select over more than one table )...for example:
SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA
WHERE SEATSOCC < F~SEATSMAX
AND NOT EXISTS ( SELECT * FROM SPFLI
WHERE CARRID = F~CARRID
AND CONNID = F~CONNID
AND CITYFROM = 'FRANKFURT'
AND CITYTO = 'NEW YORK' )
AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.
With subquery, using the not exists would return only the first table rows that did not match. A limitation of subquery is that you cannot retain any of the data from the second select....
02-13-2012 10:04 PM
... and using formatted code without use of alias AS F makes it even nicer:
SELECT * FROM SFLIGHT INTO SFLIGHT_WA
WHERE SEATSOCC < SFLIGHT~SEATSMAX
AND NOT EXISTS ( SELECT * FROM SPFLI
WHERE CARRID = SFLIGHT~CARRID
AND CONNID = SFLIGHT~CONNID
AND CITYFROM = 'FRANKFURT'
AND CITYTO = 'NEW YORK' )
AND FLDATE BETWEEN '19990101' AND '19990331'.
ENDSELECT.Regards
Clemens
02-14-2012 4:14 AM
Hi,
In ABAP joins can be used for 3 tables if there are more than 3 tables it is better to go with For All Entries, And according to your requiorement left outer join is possible in ABAP with a select statement to ignore un necessary records use where condition.
Regards,
Goutam Kolluru.
02-14-2012 10:44 AM
hiii,
you can try this below example code of left outer join query.....
DATA: CUSTOMER TYPE SCUSTOM,
BOOKING TYPE SBOOK.
SELECT SCUSTOM~NAME SCUSTOM~POSTCODE SCUSTOM~CITY
SBOOK~FLDATE SBOOK~CARRID SBOOK~CONNID SBOOK~BOOKID
INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
BOOKING-BOOKID)
FROM SCUSTOM LEFT OUTER JOIN SBOOK
ON SCUSTOM~ID = SBOOK~CUSTOMID AND
SBOOK~FLDATE = '20081015'
ORDER BY SCUSTOM~NAME SBOOK~FLDATE.
WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,
BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,
BOOKING-BOOKID.
ENDSELECT.i hope from above example you vil get some idea.....
02-14-2012 11:01 AM
Hi,
Please check this link to get an idea about left outer join in ABAP.
http://help.sap.com/saphelp_470/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm
Regards.
05-09-2012 8:01 AM
Hello,
I have the same question but unfortunately none of these replies provide the answer.
As the Original Poster says, we would like to get the non-matching rows in an outer join.
Sure, you can do it with a subquery but that could mean 10,000 selects instead of 1.
Yes, you can do it in ABAP itself but that would involve retrieving 99% useless data from the database.
Is it not possible in ABAP SQL?
We know what an outer join is, thanks.
regards
Rick
04-03-2014 11:00 AM
04-29-2016 11:06 AM
A little late to the party but in case you're still looking for an answer. First do an outer join so you get the complete set of data, including the records that have a match (the ones you don't want). Then exclude those in the where clause by means of an inner 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.
gives as result:
AA American Airlines 0000
AB Air Berlin 0000
AC Air Canada 0000
AF Air France 0000
AZ Alitalia 0000
BA British Airways 0000
CO Continental Airlines 0000
DL Delta Airlines 0000
FJ Air Pacific 0000
JL Japan Airlines 0408
LH Lufthansa 0400
LH Lufthansa 0402
LH Lufthansa 2402
NG Lauda Air 0000
NW Northwest Airlines 0000
QF Qantas Airways 0006
SA South African Air. 0000
SQ Singapore Airlines 0000
SR Swiss 0000
UA United Airlines 3517
UA United Airlines 0941
select s~carrid, s~carrname, p~connid
into corresponding fields of table @itab
from scarr as s
inner join spfli as p on s~carrid = p~carrid and p~cityfrom = 'FRANKFURT'.
gives as result:
JL Japan Airlines 0408
LH Lufthansa 2402
LH Lufthansa 0402
LH Lufthansa 0400
QF Qantas Airways 0006
UA United Airlines 3517
UA United Airlines 0941
So if you exlude that set you get the following query:
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'
where s~carrid not in ( select s~carrid
from scarr as s
inner join spfli as p on s~carrid = p~carrid and p~cityfrom = 'FRANKFURT' ).
You end up with the result you're looking for, all entries that don't have a match:
AA American Airlines 0000
AB Air Berlin 0000
AC Air Canada 0000
AF Air France 0000
AZ Alitalia 0000
BA British Airways 0000
CO Continental Airlines 0000
DL Delta Airlines 0000
FJ Air Pacific 0000
NG Lauda Air 0000
NW Northwest Airlines 0000
SA South African Air. 0000
SQ Singapore Airlines 0000
SR Swiss 0000
Still a subquery but should be a lot more efficient since you only use 2 selects.