SQ02 Left Join

[Former Member]

Hello,

I made a query inculding mara, marc and mard tables.

mara left joins marc and marc inner joins mard.(I tried marc to left join mard but it doesn't allow it because of the previous(mara-marc) left-join)

I tried to make the query in a way, even if only basic data is maintained query would bring rows from marc and mard empty.

But unfortunately for me, it doesn't work that way and only the maintained data for all tables are returning as results.

What am I doing wrong?

Thanks,

Arda

[jogeswararao_kavala]

Hi Arda,

Have you tried the other way of joins?

ie., MARA to MARC inner and MARC to MARD left.

Jogeswara Rao K

[Former Member]

Hello Jogeswara,

Yes I did but if MARA inner joins MARC unmaintained data for WERKS will not return in query.

[jogeswararao_kavala]

Hi Arda,

Actually, my difficulty is to understand the requirement, so that I could simulate and cross-check. My senses say your requirement is well within the reach, because it is not a complex join at all.

For the present issue, you may explore the feature documented here.

For other features of Infoset Queries you may refer to this document also

Jogeswara Rao K

[jogeswararao_kavala]

By referring to the 'Alias Tables' I am hinting at creating an alias table for MARC or MARD as per your requirement (by the name MARC1 or MARD1) and use this table parallelly.

Jogeswara Rao K.

[Former Member]

Jogeswara,

I thought it worked correctly but I missed a spot.

If only MARA table is maintained my query doesn't return any result.

How can I solve this? I can create an ABAP program but I want to fix this issue within SAP query limits.

Thanks.

[jogeswararao_kavala]

Unable to understand

[Former Member]

If a material is not maintained in MARC and MARD, my query doesn't return result.

I mean, let's say I maintained only basic data of material, then my query won't show that material.

[jogeswararao_kavala]

Naturally. How there could be any solution for such case?

[Former Member]

If I could do the following join,

MARA left join ( MARC left join MARD )

Wouldn't I be able to  receive this report?

	MARA	MARA	MARC	MARD
Maintainance Status	MATNR	MATKL	WERKS	LGORT
Only basic data	10000	A01
Only basic data	10001	A02
Plant and basic data	10002	A03	PLNT1
Plant and basic data	10002	A03	PLNT2
Plant and basic data	10003	A04	PLNT1
Plant and basic data	10003	A04	PLNT2
Strg, plant and basic data	10004	A05	PLNT1	STRG1
Strg, plant and basic data	10005	A06	PLNT1	STRG2
Strg, plant and basic data	10005	A06	PLNT2	STRG3
Strg, plant and basic data	10006	A07	PLNT2	STRG4

[jogeswararao_kavala]

I understood the issue

Try this join. This should work

If the above does not work, switch to Infoset Query.(If you are using SQVI).  In the infoset use one table MARA and MARC with LOJ. Have MARD fields as Additional fields and get values through Select statements in the Code section of these additional fields.

Best of Luck

Jogeswara Rao K

[Former Member]

Well in theory this join condition is wrong, because it will multiply MARD data for each MARC plant even if I did not maintain those MARD data regarding MARC plant.( as in the tables below )

But we maintain each material for only 1 plant so this should work as long as people do not make mistake and maintain same material for multiple plants.

Thanks again.

Real data
MARA	MARC	MARD
10000	PLNT1	STRG1
10000	PLNT1	STRG2
10000	PLNT2
Query result would be like
MARA	MARC	MARD
10000	PLNT1	STRG1
10000	PLNT1	STRG2
10000	PLNT2	STRG1
10000	PLNT2	STRG2

[jogeswararao_kavala]

In fact I've not worked with these tables. On the other hand when issues are there, I believe there is no harm in trying things do not fall under theories. One more thing you can do is to remove(delete) WERKS join in the original configuration.

[Former Member]

Hi Arda,

Please try

Regards

Boris

[Former Member]

Hi Boris,

It doesn't work as I intended.

I want MARC and MARD fields to return null even if they are not maintained.

And inner join doesn't allow this.

[Former Member]

MARA----------MARC----left join---MARD worked for me.

Thanks everyone

[Former Member]

This message was moderated.