Problem in fetching values from different tables in internal table

[Former Member]

Hi,

I am trying to create an internal table with five fields,

viz. <b>SHKZG, POSNR, BDMNG, MATNR</b> from table RESB and <b>MAKTX</b> from table MAKT.

tables: RESB.

data: begin of iresb occurs 100,

SHKZG like resb-SHKZG,

posnr like resb-posnr,

BDMNG like resb-BDMNG,

GMATNR like RESB-MATNR,

MAKTX like MAKT-MAKTX,

end of iresb.

SELECT <b>SHKZG POSNR BDMNG MATNR</b> into corresponding fields of table iresb from resb where aufnr = X.

Now I have to also add field MAKTX from MAKT table in my internal table apart from <b>above four fields</b>

How do I do this ? (MATNR field is common in RESB and MAKT)

<b>I tried doing following:</b>

SELECT MAKTX into corresponding fields of TABLE IRESB from MAKT inner join RESB on MAKT_{MATNR = RESB}MATNR

where aufnr = X.

<b>But then it does not correspond properly to other four fields.</b>

Tushar

[Former Member]

Hi Tushar,

You can do it as a join like this

SELECT A~SHKZG A~POSNR A~BDMNG 
       A~MATNR B~MAKTX
  FROM RESB AS A INNER JOIN
       MAKT AS B ON B~MATNR = A~MATNR
  INTO TABLE iresb
 WHERE A~AUFNR = 'X'
   AND B~SPRAS = SY-LANGU.

or a better way to do is to have another internal table

DATA: begin of imakt occurs 0,
        matnr LIKE makt-matnr,
        maktx LIKE makt-maktx.
DATA: end of imakt.

SELECT MATNR MAKTX FROM MAKT
                   INTO TABLE imakt
                    FOR ALL ENTRIES IN iresb
                  WHERE matnr = ireb-matnr
                    AND spras = sy-langu.

After that you can read this 'imakt' table whenever you need the description of the material.

Srinivas

[Former Member]

Use this query instead of first query and remove the second query.

SELECT SHKZG POSNR BDMNG MATNR MAKTX into corresponding fields of table iresb from

resb JOIN MAKT

ON RSEB_{MATNR = MAKT}MATNR

where aufnr = X

and spras = 'EN'. or you can use sy-langu.

consider language also when reading makt.

regards,

Gagan

[Former Member]

Gagan,

If I try according to what you have said it gives error,

"Column name "MATNR" has two meanings.ABAP4 Open SQL Statement"

[Former Member]

Tushar, did you try mine?

The syntax you got using Gagan's code was because MATNR is there both in RESB and MARA. In such cases you have to use aliases as I mentioned in my post.

Srinivas

[Former Member]

Hi,

I tried your code as following:

report ZTF1.

tables: RESB, MAKT.

data: begin of iresb occurs 100,

SHKZG like resb-SHKZG,

posnr like resb-posnr,

BDMNG like resb-BDMNG,

MATNR like RESB-MATNR,

end of iresb.

SELECT SHKZG POSNR BDMNG MATNR into corresponding fields of table iresb

from resb where aufnr = X.

DATA: begin of imakt occurs 0,

matnr LIKE makt-matnr,

maktx LIKE makt-maktx.

DATA: end of imakt.

SELECT MATNR MAKTX FROM MAKT

INTO TABLE imakt

FOR ALL ENTRIES IN iresb

WHERE matnr = iresb-matnr

AND spras = sy-langu.

loop at iresb.

WRITE:/ IRESB-POSNR.

WRITE:/ IRESB-MATNR.

WRITE:/ IRESB-BDMNG.

<b>WRITE:/ IMAKT-MAKTX.</b>

ENDIF.

endloop.

But how do I PRINT MAKTX because IMAKT-MAKTX does not get printed ?

[Former Member]

Hi Shrinivas,

I know that for printing MAKTX field we have to loop at IMAKT but in my case I want to print only values where IRESB-SHKZG is 'S'.

And this cannot be satisfies if we have to loop on imakt second time.

Can you help in this regard ?

I will surely awrad reward points.

[RichHeilman]

In that case, you would have to at least read a line from the IMAKT table. This why you are not getting anything.

Do something like this.

<b>

loop at iresb.

WRITE:/ IRESB-POSNR.
WRITE:/ IRESB-MATNR.
WRITE:/ IRESB-BDMNG.

Read table iMAKT with key matnr = iresb-matnr.
if sy-subrc = 0.
WRITE:/ IMAKT-MAKTX.
endif.

endloop.

</b>

Regards,

Rich Heilman

[Former Member]

Sorry, The query should be like this

SELECT SHKZG POSNR BDMNG RSEB~MATNR MAKTX into corresponding fields of table iresb from

resb JOIN MAKT

ON RSEB_{MATNR = MAKT}MATNR

where aufnr = X

and spras = 'EN'. or you can use sy-langu.

Now just loop at irseb and write the required fields. You will have maktx for all the rows. No need for second internal table for MAKTX.

Regards,

Gagan