topic Re: SQ02 InfoSet Left Join Problem in Application Development and Automation Discussions

SQ02 InfoSet Left Join Problem

bkilboy — Fri, 15 Jul 2016 19:48:34 GMT

Hi, I'm not sure if this is the correct area to post this, if not please let me know.

In SQ02 I have an InfoSet table left join but the records are only returning as if it's an inner join (Only records that are in both tables). Any ideas? My join screenshot is below and attached in case you can't see enough below..

Re: SQ02 InfoSet Left Join Problem

Jelena_Perfiljeva — Fri, 15 Jul 2016 20:20:28 GMT

I believe this effect occurs when you add a field from the left joined table to the selection screen in query. In ABAP, we cannot actually add WHERE condition to SELECT for such tables, but since with the queries we maintain JOIN in the infoset and selection is usually in the query then it does not trigger any warnings.

If you try to use the left joined table in the selection then JOIN will, essentially behave as INNER, not LEFT.

Re: SQ02 InfoSet Left Join Problem

jogeswararao_kavala — Fri, 15 Jul 2016 20:34:14 GMT

Bresnt

These issues you need to resolve by different combinations or gradual joining of tables. First try with 3 tables and see the behavior. (MARD, MARA, EINA).

But I doubt whether you are using right table (EINA) for your requirement? Whether you need to EKKO instead?

Regards

KJogeswaraRao

Re: SQ02 InfoSet Left Join Problem

Sandra_Rossi — Fri, 15 Jul 2016 20:57:53 GMT

When you have a inner join on a table with left outer join (EINA-LFA1), then the left outer join behaves like a inner join. I think that's because when there's no record in EINA, a dummy record of EINA is formed with "null" fields, consequently no record corresponds in LFA1, and finally there's no record for the EINA-LFA1 combination.

The first solution is to remove table LFA1, or to write several SELECT directly via ABAP using a "data reading program".

Re: SQ02 InfoSet Left Join Problem

bkilboy — Fri, 15 Jul 2016 21:23:36 GMT

I tried making a new query with it using no parameters this time and same issue. So I don't think it's that. Sounds like it's more about it being joined to another table after the left join like someone said below I guess.

Re: SQ02 InfoSet Left Join Problem

bkilboy — Fri, 15 Jul 2016 21:37:02 GMT

Sure enough, if I do a test without LFA1 it works fine. But that's unfortunate because then I can't get Vendor name in my query. (And to the guy above, yes, I need EINA because I need the Vendor Material Number that is in that table).

I was hoping not to have to go to an ABAP program, as I'm not an ABAPer, I can just do queries. I wonder is there any other way, you can't do sub queries right? Like to join together those 2 tables first and then have that be the only piece that I left join against?

Or maybe a custom field that just has that vendor name so I don't actually have the table as part of the join?

Re: SQ02 InfoSet Left Join Problem

JL23 — Fri, 15 Jul 2016 21:52:14 GMT

the docu Left Outer Join - Modeling - SAP Library says it: Tables connected with left outer joins always form-figuratively speaking- the end of a chain of tables.

Re: SQ02 InfoSet Left Join Problem

Clemenss — Fri, 15 Jul 2016 21:53:48 GMT

try left join for MBEW

Regards Clemens

Re: SQ02 InfoSet Left Join Problem

Jelena_Perfiljeva — Fri, 15 Jul 2016 22:21:35 GMT

Sorry, I didn't notice that you had a two table chain going. Yes, with LEFT JOIN we can't really just keep joining more tables.

But if you are only interested in the vendor name and material description then you don't need either LFA1 or MAKT. Unless you've purposefully unchecked the text flag when creating the infoset, the corresponding texts will be pulled automatically and will be available in the query. You won't see them in the infoset, but when you go to create a query you'll notice the additional field section at the bottom with many text fields available. Material description and vendor name should be there.

In addition to the blog Jurgen mentioned, there are many query-related blogs and documents on SCN that you should find helpful.

Re: SQ02 InfoSet Left Join Problem

bkilboy — Fri, 15 Jul 2016 22:27:56 GMT

Thank you, I will try that on Tuesday, but it sounds like it will work!

Re: SQ02 InfoSet Left Join Problem

bkilboy — Tue, 19 Jul 2016 16:41:18 GMT

The additional fields for the descriptions worked and solved my issue too completely now. Thank you!