Skip to Content

Join help

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!

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jan 09, 2014 at 01:52 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 08, 2014 at 11:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 09, 2014 at 02:11 PM

    Hi Ashley,

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

    BR.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.