topic Re: retrieving data from 3 tables in Application Development and Automation Discussions

retrieving data from 3 tables

Former Member — Thu, 27 Sep 2007 11:00:22 GMT

Hi,

I am working on a report where I have to retrieve data from 3 tables(marc,mara,makt).For this I have used 3 select statements for retriving data from 3 tables.There r some fields which r not present in marc table where as present in mara and makt tables.So added thos fields in marc internal table t_marc.I have looped at marc table and used the read statement to read the data from mara table using the key field matnr from both the tables.This read statement is giving the sy-subrc value as 4.Could u pls tell me why this is happening?I am sending my code.

SELECT matnr

werks

pstat

lvorm

FROM marc

INTO TABLE t_marc

PACKAGE SIZE 500

WHERE matnr IN s_matnr

AND werks IN s_werks.

IF NOT t_marc[] IS INITIAL.

SELECT matnr

ersda

laeda

meins

FROM mara

INTO TABLE t_mara

FOR ALL ENTRIES IN t_marc

WHERE matnr = t_marc-matnr

AND ersda IN s_ersda

AND laeda IN s_laeda.

IF NOT t_mara[] IS INITIAL.

SELECT matnr

maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr = t_mara-matnr.

LOOP AT t_marc INTO wa_marc.

READ TABLE t_mara INTO wa_mara WITH KEY matnr = t_marc-matnr.

IF sy-subrc IS INITIAL.

wa_marc-meins = wa_mara-meins.

MODIFY t_marc.

APPEND wa_marc TO t_output.

ENDIF.

READ TABLE t_makt INTO wa_makt WITH KEY matnr = t_marc-matnr.

IF sy-subrc IS INITIAL.

wa_marc-maktx = wa_makt-maktx.

MODIFY t_marc.

APPEND wa_marc TO t_output.

ENDIF.

ENDLOOP.

Re: retrieving data from 3 tables

Former Member — Thu, 27 Sep 2007 11:05:42 GMT

Hi,

U set Break-point & debug the code .Then u can find out where the Problem occurs.

Regards,

Padmam.

Re: retrieving data from 3 tables

Former Member — Thu, 27 Sep 2007 11:05:53 GMT

hi hema,

Dont use three select stements for this scenario.

Just use innerjoin and join all the three tables.

Three tables should contain MATNR (material number).

By using that retrieve data from 3 tables by using a single select statement.

Eg:

SELECT DISTINCT a~matnr    "MATERIAL NUMBER
         a~ersda    "CREATED ON
         a~mtart    "MATERIAL TYPE
         a~meins    "BASE UNIT OF MEASURE
         a~bstme    "ORDER UNIT
         b~maktx    "MATERIAL DESCRIPTION
         c~bwkey    "VALUATION AREA
         c~lbkum    "TOTAL VALUED STOCK
         c~salk3    "VALUE OF TOTAL VALUED STOCK
         c~stprs    "STANDARD PRICE
         c~peinh    "PRICE UNIT
         c~bklas    "VALUATION CLASS
         INTO TABLE it_matdetails
         FROM mara AS a INNER JOIN makt AS b ON a~matnr = b~matnr
                        INNER JOIN mbew AS c ON a~matnr = c~matnr
                        INNER JOIN marc AS d ON a~matnr = d~matnr
                        INNER JOIN mard AS e ON a~matnr = e~matnr
         WHERE b~spras = sy-langu AND a~matnr IN matnr AND d~werks IN werks AND e~lgort IN lgort.

pls reward if helpful.

Re: retrieving data from 3 tables

Former Member — Thu, 27 Sep 2007 11:10:31 GMT

small mistake you done.

just do this.

SELECT matnr

werks

pstat

lvorm

FROM marc

INTO TABLE t_marc

PACKAGE SIZE 500

WHERE matnr IN s_matnr

AND werks IN s_werks.

IF NOT t_marc[] IS INITIAL.

SELECT matnr

ersda

laeda

meins

FROM mara

INTO TABLE t_mara

FOR ALL ENTRIES IN t_marc

WHERE matnr = t_marc-matnr

AND ersda IN s_ersda

AND laeda IN s_laeda.

IF NOT t_mara[] IS INITIAL.

SELECT matnr

maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr = t_mara-matnr.

LOOP AT t_marc INTO wa_marc.

<b>READ TABLE t_mara INTO wa_mara WITH KEY matnr = wa_marc-matnr.</b>

IF sy-subrc IS INITIAL.

wa_marc-meins = wa_mara-meins.

MODIFY t_marc.

APPEND wa_marc TO t_output.

ENDIF.

<b>READ TABLE t_makt INTO wa_makt WITH KEY matnr = wa_marc-matnr.</b>

IF sy-subrc IS INITIAL.

wa_marc-maktx = wa_makt-maktx.

MODIFY t_marc.

APPEND wa_marc TO t_output.

ENDIF.

ENDLOOP.

Re: retrieving data from 3 tables

Former Member — Thu, 27 Sep 2007 11:12:50 GMT

LOOP AT t_marc INTO wa_marc.

READ TABLE t_mara INTO wa_mara WITH KEY matnr = t_marc-matnr.

Don't u think this should be

READ TABLE t_mara INTO wa_mara WITH KEY matnr = wa_marc-matnr.

because u are taking values from t_marc into wa_marc

Re: retrieving data from 3 tables

Former Member — Thu, 27 Sep 2007 11:13:05 GMT

good

i think you r doing some mistake in the if statement ,check that in the debugging mode and change accordingly.

thanks

mrutyun^

Re: retrieving data from 3 tables

mahaboob_pathan — Thu, 27 Sep 2007 11:37:38 GMT

SELECT matnr

werks

pstat

lvorm

FROM marc

INTO TABLE t_marc

PACKAGE SIZE 500

WHERE matnr IN s_matnr

AND werks IN s_werks.

IF NOT t_marc[] IS INITIAL.

SELECT matnr

ersda

laeda

meins

FROM mara

INTO TABLE t_mara

FOR ALL ENTRIES IN t_marc

WHERE matnr = t_marc-matnr

AND ersda IN s_ersda

AND laeda IN s_laeda.

IF NOT t_mara[] IS INITIAL.

SELECT matnr

maktx

FROM makt

INTO TABLE t_makt

FOR ALL ENTRIES IN t_mara

WHERE matnr = t_mara-matnr.

LOOP AT t_marc INTO wa_marc.

READ TABLE t_mara INTO wa_mara WITH KEY matnr = wa_marc-matnr.

IF sy-subrc IS INITIAL.

wa_marc-meins = wa_mara-meins.

MODIFY t_marc.

APPEND wa_marc TO t_output.

ENDIF.

READ TABLE t_makt INTO wa_makt WITH KEY matnr = wa_marc-matnr.

IF sy-subrc IS INITIAL.

wa_marc-maktx = wa_makt-maktx.

MODIFY t_marc.

APPEND wa_marc TO t_output.

ENDIF.

ENDLOOP.