Join help

[former_member238775]

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!

[custodio_deoliveira]

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

[former_member238775]

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!

[Ryan-Crosby]

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

[former_member238775]

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. 

[Ryan-Crosby]

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.

[ThomasZloch]

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

[Former Member]

Hi Ashley,

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

BR.