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

SQ02 Left Join

Former Member — Tue, 25 Feb 2014 18:02:49 GMT

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

Re: SQ02 Left Join

jogeswararao_kavala — Wed, 26 Feb 2014 08:30:01 GMT

Hi Arda,

Have you tried the other way of joins?

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

Jogeswara Rao K

Re: SQ02 Left Join

Former Member — Wed, 26 Feb 2014 09:07:16 GMT

Hi Arda,

Please try

Regards

Boris

Re: SQ02 Left Join

Former Member — Wed, 26 Feb 2014 09:54:20 GMT

Hello Jogeswara,

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

Re: SQ02 Left Join

jogeswararao_kavala — Wed, 26 Feb 2014 11:10:41 GMT

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

Re: SQ02 Left Join

jogeswararao_kavala — Wed, 26 Feb 2014 11:31:06 GMT

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.

Re: SQ02 Left Join

Former Member — Thu, 27 Feb 2014 06:52:58 GMT

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.

Re: SQ02 Left Join

Former Member — Thu, 27 Feb 2014 06:56:42 GMT

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

Thanks everyone

Re: SQ02 Left Join

Former Member — Thu, 27 Feb 2014 07:43:17 GMT

This message was moderated.

Re: SQ02 Left Join

Former Member — Fri, 25 Jul 2014 06:59:56 GMT

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.

Re: SQ02 Left Join

jogeswararao_kavala — Fri, 25 Jul 2014 07:22:28 GMT

Unable to understand

Re: SQ02 Left Join

Former Member — Fri, 25 Jul 2014 07:33:25 GMT

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.

Re: SQ02 Left Join

jogeswararao_kavala — Fri, 25 Jul 2014 07:38:07 GMT

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

Re: SQ02 Left Join

Former Member — Fri, 25 Jul 2014 08:11:04 GMT

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

Re: SQ02 Left Join

jogeswararao_kavala — Fri, 25 Jul 2014 08:29:49 GMT

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

Re: SQ02 Left Join

Former Member — Fri, 25 Jul 2014 08:44:39 GMT

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

Re: SQ02 Left Join

jogeswararao_kavala — Fri, 25 Jul 2014 08:48:03 GMT

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.