topic Re: Join help in Application Development and Automation Discussions

Join help

Former Member — Wed, 08 Jan 2014 18:17:45 GMT

I am relatively new to ABAP and am needing help with a join statement as it contains multiple tables. I am trying to just simply display certain fields across MARA, MARC, MVKE, and MAKT based on their material numbers. I have the ACCOUNT ASSIGNMENT GROUP field displayed and would like to list its description along with it. I know that this is in table TVKMT and have tried to figure out a solution to get the correct amount of rows. I currently have a working version that joins MARA, MVKE, MARC, and MAKT and then just loops through to get the AAG description as seen here:

<code>

*Put data into internal table

SELECT marc~werks mvke~matnr mara~mtart makt~maktx marc~beskz mvke~vkorg mvke~vtweg mara~spart mvke~ktgrm

FROM ( mvke

INNER JOIN mara

ON mara~matnr = mvke~matnr

INNER JOIN marc

ON marc~matnr = mara~matnr

INNER JOIN makt

ON makt~matnr = marc~matnr )

INTO CORRESPONDING FIELDS OF TABLE dt_materials

WHERE makt~spras IN spras

AND marc~werks IN werks

AND mvke~ktgrm IN ktgrm

AND mvke~matnr IN matnr

AND mvke~vkorg IN vkorg

AND mvke~vtweg IN vtweg.

* Gets Account Assignment Group Description

LOOP AT dt_materials INTO ds_materials.

tab_index = sy-tabix.

SELECT SINGLE tvkmt~vtext

INTO ds_materials-vtext

FROM tvkmt

WHERE tvkmt~ktgrm = ds_materials-ktgrm

and tvkmt~spras in spras.

IF sy-subrc EQ 0.

MODIFY dt_materials FROM ds_materials INDEX tab_index TRANSPORTING vtext.

ENDIF.

ENDLOOP.

</code>

I tried doing another join like this:

<code>

SELECT marc~werks mvke~matnr mara~mtart makt~maktx marc~beskz mvke~vkorg mvke~vtweg mara~spart mvke~ktgrm tvkmt~vtext

FROM ( mara

INNER JOIN marc

ON marc~matnr = mara~matnr

INNER JOIN makt

ON makt~matnr = marc~matnr

INNER JOIN mvke

ON mvke~matnr = makt~matnr

JOIN tvkmt

ON tvkmt~ktgrm = mvke~ktgrm )

INTO CORRESPONDING FIELDS OF TABLE dt_materials

WHERE makt~spras IN spras

AND tvkmt~spras IN spras

AND marc~werks IN werks

AND mvke~ktgrm IN ktgrm

AND mvke~matnr IN matnr

AND mvke~vkorg IN vkorg

AND mvke~vtweg IN vtweg.

</code>

I've played around with different types of joins for the last one and can't seem to get the correct output as the above code. I don't know if this is an overkill explanation for such a simple solution, but anyway...help would be appreciated. Thanks in advance!

Re: Join help

custodio_deoliveira — Wed, 08 Jan 2014 23:21:16 GMT

Hi Ashley,

I did not try your code, but the only "problem" I see on the second join is the language as a select-options. Are you sure you want multiple language? Another option would be to have the texts (from MAKT and TVKMT) as a LEFT JOIN, so you would still get a result if the description is not found:

SELECT marc~werks mvke~matnr mara~mtart makt~maktx marc~beskz mvke~vkorg mvke~vtweg mara~spart mvke~ktgrm tvkmt~vtext

FROM ( mara

INNER JOIN marc

ON marc~matnr = mara~matnr

LEFT JOIN makt

ON makt~matnr = marc~matnr

INNER JOIN mvke

ON mvke~matnr = makt~matnr

LEFT JOIN tvkmt

ON tvkmt~ktgrm = mvke~ktgrm )

INTO CORRESPONDING FIELDS OF TABLE dt_materials

WHERE makt~spras IN spras

AND tvkmt~spras IN spras

AND marc~werks IN werks

AND mvke~ktgrm IN ktgrm

AND mvke~matnr IN matnr

AND mvke~vkorg IN vkorg

AND mvke~vtweg IN vtweg.

Cheers,

Custodio

Re: Join help

Former Member — Thu, 09 Jan 2014 13:19:36 GMT

That was to make sure the description was the same language as what's already in the internal table, but I think that join may work for me. Thanks for your help!

Re: Join help

Ryan-Crosby — Thu, 09 Jan 2014 13:33:59 GMT

Hi Ashley,

It appears you are going to get the cartesian product between the tables MVKE & MARC when you are joining only on MATNR. I'm not that familiar with MVKE but maybe you can use the field DWERK (delivering plant) for your join on MARC to tighten up the results. Specifying that extra join condition would mean that you'd only get one row from MARC for each row of MVKE instead of multiplying the set results.

Regards,

Ryan Crosby

Re: Join help

Former Member — Thu, 09 Jan 2014 13:44:05 GMT

I need MVKE to get the Distribution Channel. This information is a compilation of fields for accounting purposes so that they can check everything they need all in one place. I'm trying to make it as descriptive as possible so it can all be accessed from one place instead of having to jump around.

Re: Join help

ThomasZloch — Thu, 09 Jan 2014 13:52:20 GMT

I'm not happy with SPRAS being a selection range, this might make your result set explode if several languages are selected, or if it is empty altogether.

At least tvkmt~vtext should probably be displayed in the logon language only, then you can include tvkmt~spras = sy-langu in the ON-condition for TVKMT and see if this helps.

You will still see a row per maintained MAKT language if range SPRAS is not selecting just one language.

Thomas

Re: Join help

Former Member — Thu, 09 Jan 2014 14:11:17 GMT

Hi Ashley,

See the join results using SQVI transaction. It gives a pictorial view.

BR.

Re: Join help

Ryan-Crosby — Thu, 09 Jan 2014 15:39:54 GMT

Without restricting your join to the full key you will never eliminate the extra rows though. So you'd either have to stick with the extra rows and try to remove them later or make a change to your join conditions.