"Left Outer Join" with "For All Entries" condition

[former_member319444]

Hello,

Maybe you can help me.

I want to do an Select like this:

	SELECT a~matnr c~werks a~wrkst a~groes c~ekgrp
	INTO CORRESPONDING FIELDS OF TABLE lt_data_info
	FROM mara AS a
	left OUTER JOIN marc AS c
	ON   a~matnr = c~matnr
	FOR ALL ENTRIES IN ct_data_info
	WHERE a~matnr = ct_data_info-matnr
	AND  c~werks = ct_data_info-werks.

But this does not work.

So I tried this:

	SELECT a~matnr c~werks a~wrkst a~groes c~ekgrp
	INTO CORRESPONDING FIELDS OF TABLE lt_data_info
	FROM mara AS a
	left OUTER JOIN marc AS c
	ON   a~matnr = c~matnr
	AND  c~werks = ct_data_info-werks
	FOR ALL ENTRIES IN ct_data_info
	WHERE a~matnr = ct_data_info-matnr.

It does not work.

So how can I do such Select.

Thanks,

Pascal

Sorry for my bad English, here the German version.

Entschuldigung für mein schlechtes Englisch, hier die deutsche Version.

Hallo,

Vielleicht könnt ihr mir ja helfen.

Ich möchte einen Select wie diesen machen.

	SELECT a~matnr c~werks a~wrkst a~groes c~ekgrp
	INTO CORRESPONDING FIELDS OF TABLE lt_data_info
	FROM mara AS a
	left OUTER JOIN marc AS c
	ON   a~matnr = c~matnr
	FOR ALL ENTRIES IN ct_data_info
	WHERE a~matnr = ct_data_info-matnr
	AND  c~werks = ct_data_info-werks.

Allerdings funktioniert der so nicht.

Daher habe ich folgendes probiert, welches allerdings auch nicht funktioniert hat.

	SELECT a~matnr c~werks a~wrkst a~groes c~ekgrp
	INTO CORRESPONDING FIELDS OF TABLE lt_data_info
	FROM mara AS a
	left OUTER JOIN marc AS c
	ON   a~matnr = c~matnr
	AND  c~werks = ct_data_info-werks
	FOR ALL ENTRIES IN ct_data_info
	WHERE a~matnr = ct_data_info-matnr.

Diese Überlegung hat auch nichts gebracht.

Ich hoffe, dass ihr mir weiterhelfen könnt.

Mit freundlichen Grüßen,

Pascal

[former_member185116]

hi pascal,

you can not use JOINS with for all entries satatement...

[former_member319444]

Hello,

Is there no way to do this. Or do I really need two selects.

[former_member185116]

select data from mara into it_mara...

if it_mara[] is not initial.

select * from marc

             into table it_marc

             for all entries in marc

             where matnr eq it_mara-matnr.

endif.

[Former Member]

Hi Pascal

It depends...

Do you just want to make the first select work? Then you can write it like this:

SELECT a~matnr c~werks a~wrkst a~groes c~ekgrp

    INTO CORRESPONDING FIELDS OF TABLE lt_data_info

    FROM marc AS c

    left OUTER JOIN mara AS a

      ON   a~matnr = c~matnr

    FOR ALL ENTRIES IN ct_data_info

    WHERE c~matnr = ct_data_info-matnr

     AND  c~werks = ct_data_info-werks.

However, if you are talking about the idea of using a 'for all entries in' on a field that is from a table that is joined by a left outer join, then that will not work. Except if have ABAP version 7.40 on your system, then is is possible, but you have to write it like this:

SELECT a~matnr, c~werks, a~wrkst, a~groes, c~ekgrp

    INTO CORRESPONDING FIELDS OF TABLE @lt_data_info

    FROM mara AS a

    left OUTER JOIN marc AS c

      ON   a~matnr = c~matnr

    FOR ALL ENTRIES IN @ct_data_info

    WHERE a~matnr = @ct_data_info-matnr

     AND  c~werks = @ct_data_info-werks.

(OpenSQL syntax: ABAP Keyword Documentation)

[Chintu6august]

Hi,

we can make use of JOINS with FOR ALL ENTRIES and you must make use of INNER JOIN because you will read those records which are present in MARA as well as in MARC.

or you can use separate select query on both the tables and populate their data in the final structure.

thank you!!

[former_member319444]

Thank you
Your first Select is not what I search.
And with an 7.31 System, I couldn't write it like the second Select.

But if we upgrade our System, than i could change it

[former_member319444]

Not all of our Materials in MARA are although in MARC, so I think that i have to Select them seperate and merge them together.

So no Left outer Join with For all Entries.

[Sandra_Rossi]

Pascal Lammers wrote:
So no Left outer Join with For all Entries.

Of course, this sentence is valid only in your context, as it's generally possible to use left outer join with for all entries, but you can join the FAE table with only the left table before 7.40. So the answer by Jasper Debie is fully correct (both OpenSQL), and the answer by Amit Kumar is the answer to your question (use 2 SELECT).

[Former Member]

Hi Pascal,

             You can use 'inner join' if you need those records which must be present in MARC. otherwise, you can use separate select statement for mara and marc.

Regards,

Amit Kumar

[raymond_giuseppi]

Why do you use a LEFT join, you cannot use the field of such joined (right) table in  a WHERE statement so MUST use them in the ON statement, and there are millions of threads on google on difference between result of an ON on WHERE clause.

Can you elaborate: In which case do you want to get a MARA record data for which there is no data in MARC with full key.

Regards,

Raymond