left outer join query (in SQVI)

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
lsheetrit45
Posts: 15
Joined: Mon Sep 27, 2010 1:23 pm

left outer join query (in SQVI)

Post by lsheetrit45 » Wed Oct 06, 2010 8:51 am

I'm having trouble with a simple left outer join in SQVI (I'm no ABAPer, just a simple SAP user, so I'm sure you guys can solve this without a problem)

My query connects several tables (Namely: EKKO is connected to EKPO which is connected to both EKET and EKES. EKKO is also connected to LFA1)

EKPO is joined to EKES by a left outer join. (Every other join is an inner join)

I would like to input selections for fields from several tables, including EKES. (Namely: I want to view vendor confimrations for several Purchase Orders)
I would like to input 10 PO numbers and vendor confirmation category "LA" (EKES-EBTYP) and see all 10 POs and whether or not they have an "LA" confirmation.
Instead what happens when I input PO numbers and "LA" in my search criteria, the only output is those POs with an existing "LA"s, the POs without an "LA" are ommitted. If I run the query with just the PO numbers as selections (and no confirmation catergory), I get a line for every type of PO-vendor confirmation (I only want to see POs with an "LA" and POs without an "LA"- I dont want to see rows for the other confirmation types).

Hope that is a clear explanation. Please help!

AndyW
Posts: 70
Joined: Wed Jan 02, 2008 10:27 am
Location: North Norfolk, UK

Re: left outer join query (in SQVI)

Post by AndyW » Wed Oct 06, 2010 10:16 am

Hi

The reason you are not getting results with EKES-EBTYP <> "LA" is because you are specifically requesting those lines where EKES-EBTYP = "LA". :)

I think you have mis-understood what a left outer join does. It will return all rows from the left hand table that match your selection criteria even if there is not a correpsonding record in the joined to table (in this case EKES). That is to say that if you have a record in EKKO but there isn't a correpsonding record in EKES, then a left outer join will return still return those rows for EKKO whereas an Inner Join will not.

In any case, your requirement isn't entirely clear.
I only want to see POs with an "LA" and POs without an "LA"- I dont want to see rows for the other confirmation types
If you want POs with and without an LA then that will be all the POs :)

Do you mean you want all POs with an LA and all POs where there is not value at all for EKES-EBTYP? If so then specify this in your selection criteria i.e. EBTYP = 'LA' or EBTYP = ''. This will exclude all records where EBTYP has a value that is not "LA". You will probably also want to make that join an inner join rather than a left outer join unless you ahve a specific reason not to do so.

I hope that helps!
Andy

lsheetrit45
Posts: 15
Joined: Mon Sep 27, 2010 1:23 pm

Re: left outer join query (in SQVI)

Post by lsheetrit45 » Wed Oct 06, 2010 1:30 pm

i
Last edited by lsheetrit45 on Wed Oct 06, 2010 3:13 pm, edited 1 time in total.

lsheetrit45
Posts: 15
Joined: Mon Sep 27, 2010 1:23 pm

Re: left outer join query (in SQVI)

Post by lsheetrit45 » Wed Oct 06, 2010 3:12 pm

AndyW, I apologize for the confusion but you understood exactly what I'm looking for "all POs with an LA and all POs where there is not value at all for EKES-EBTYP"

Thank you for that!

Although I'm still having trouble with the selection criteria for <>"LA" you said
EBTYP = ''.
I have tried . or "" or ." or ".

No good on any of those :(
Please help!

AndyW
Posts: 70
Joined: Wed Jan 02, 2008 10:27 am
Location: North Norfolk, UK

Re: left outer join query (in SQVI)

Post by AndyW » Thu Oct 07, 2010 4:12 am

Sorry, I guess I wasn't clear either! Just do it like you would for a normal selection option on the query selection screen ie on the inclusive single vals tab enter LA on one line and on the second select the '=' sign and leave the value blank.

Andy

Post Reply