table join MARA , AUSP , MAKT, MARD

[Former Member]

Hello Everyone,

i am facing as issue while creating a sap query.

i have to create a sap query to display material number, description , characgterisctic values and values in storage locations.

forthis i have to use tables AUSP, MARA , MAKT ,MARD

i do not know how to join ausp-objek=mara-matnr.

i have searched the forum but cannot get any satisfying answer . do i have to write any codes . what that code will be etc .....

plz help

i will be very thankful to all of you .

[Former Member]

Hi,

To join these two tables you have to take care of KLART field in AUSP table.If its value is '001' (means if you want to fetch material class data for a particular material), then you can directly put condition ausp-objek = mara-matnr.

Regards,

Sunny Desai

[Former Member]

Hi,

Use tables in this way:

MARA - AUSP- CABN - MAKT - MARD.

MARA-MATNR = AUSP-OBJEK.

MARA-MATNR = MAKT-MATNR.

MARA-MATNR = MARD-MATNR.

CABN table contains all characteristics maintained in the system.

Regards,

Praveen

[Former Member]

Hi ,

Thanks sunny and praveen for the answer .

i cannot join ausp-objek=mara-matnr , it is a illegal joined .

i have tried all possible ways but cannot join .

plz help.

joseph

[former_member193382]

Hi,

I think you are aware of SQVI tcode. Use it and see which table can be used with inner join.

Regards,

Raj

[Former Member]

thanks raj,

i heard of inner join but i do not know ...searched in the forum but cannot get anything ....could you explain in detail

[Former Member]

Dear Joseph,

You have to do like this:

First goto MARA,select matnr from there then in AUSP,use objek like matnr with select query,then select particular characteristics.

Regards,

Praveen

[Former Member]

you could do the join from MARA to MAKT to MARD with the join-option.

just try to put table AUSP to EXTRAS -> ADDITIONAL TABLE -> ... and specify the OBJEK = MATNR. see that you put the full MATNR (lenght 18) there.

I did not try this, so don't damn me, if it does not work ... it's just an idea.

[Former Member]

i tried to follow your method ....but it did not work .

anybody any help

joseph

[Former Member]

I have been looking at this finally.

this will never work in a query. the reason is, that field OBJEK does not have the same format/lenght as MATNR. Assigning table AUSP in EXTRAS as I wrote - makes no sense - I do apologize!, since you can have 1-n AUSP-Entries for one OBJEK. If you want this, you have to have a 'real join' and that requires abapping one.

[Former Member]

MARA-MATNR = MCH1-MATNR

MCH1-CUOBJ_BM = AUSP-OBJEK

[Former Member]

Hi,

i do not know how to join ausp-objek=mara-matnr.

Here is one example of Joining these tables :

SELECT A~MATNR A~MEINS 
             B~WERKS B~LGORT B~CLABS B~CHARG
             C~CUOBJ_BM C~VFDAT C~HSDAT 
             E~MAKTX 
             INTO CORRESPONDING FIELDS OF TABLE ITAB 
              FROM MARA AS A INNER JOIN MCHB AS B ON A~MATNR = B~MATNR 
                                            INNER JOIN MCH1 AS C ON B~MATNR = C~MATNR                                                                                
AND B~CHARG = C~CHARG 
                                            INNER JOIN AUSP AS D ON C~CUOBJ_BM = D~OBJEK 
                                            INNER JOIN MAKT AS E ON A~MATNR = E~MATNR 
              WHERE A~MATNR IN S_MATNR 
                   AND B~WERKS IN S_WERKS 
                   AND B~CLABS > 0
                   AND B~CHARG IN S_CHARG 
                   AND B~LGORT IN S_LGORT.

Hope this helps.

Regards,

Chandravadan