topic Re: extracting data from multiple tables in Application Development and Automation Discussions

extracting data from multiple tables

Former Member — Sat, 09 Aug 2008 06:05:26 GMT

how to extract data from multiple tables? plz give sample code

Re: extracting data from multiple tables

Former Member — Sat, 09 Aug 2008 06:12:37 GMT

Hi ,

You can use inner joins and outer joins..

See this :

SELECT a_{mblnr a}mjahr a_{zeile a}bwart a_{matnr a}werks a~lgort

a_{sobkz a}menge a_{meins b}budat

FROM mseg AS a INNER JOIN mkpf AS b

ON a_{mblnr = b}mblnr

AND a_{mjahr = b}mjahr

INTO TABLE it_mseg

FOR ALL ENTRIES IN it_mara

WHERE a~bwart IN s_bwart

AND a~matnr EQ it_mara-matnr

AND a~werks = p_werks

AND b~budat >= v_start

AND b~budat <= v_finish.

regards,

Nishant

Re: extracting data from multiple tables

Former Member — Sat, 09 Aug 2008 06:19:56 GMT

hi,

if u want to extract just use the following code.

select matnr mbrsh mtart from mara into table i_mara where matnr in s_matnr.

if not i_mara[] is initial.

select matnr werks from marc into table i_marc for all entries in i_mara where matnr = i_mara-matnr.

endif.

i hope this can be useful to you.

Re: extracting data from multiple tables

Former Member — Sat, 09 Aug 2008 06:20:12 GMT

Hii!

Check this sample code. Here I'm fetching the data from two tables


REPORT z_sdn.
TABLES:
  sflight.

SELECT-OPTIONS:
  s_carrid FOR sflight-carrid,
  s_connid FOR sflight-connid.

DATA:
  BEGIN OF fs_flight,
    carrid   TYPE sflight-carrid,
    connid   TYPE sflight-connid,
    fldate   TYPE sflight-fldate,
    bookid   TYPE sbook-bookid,
    customid TYPE sbook-customid,
  END OF fs_flight.

DATA:
  t_flight LIKE
     TABLE OF
           fs_flight.


START-OF-SELECTION.

SELECT f~carrid
       f~connid
       f~fldate
       b~bookid
       b~customid
  FROM ( sflight AS f
         INNER JOIN sbook AS b ON f~carrid = b~carrid
                              AND f~connid = b~connid
                              AND f~fldate = b~fldate )
  INTO TABLE t_flight
 WHERE f~carrid IN s_carrid
   AND f~connid IN s_connid.

 LOOP AT t_flight INTO fs_flight.
   WRITE: / fs_flight-carrid,
            fs_flight-connid,
            fs_flight-fldate,
            fs_flight-bookid,
            fs_flight-customid.
 ENDLOOP.

Regards

Abhijeet

Re: extracting data from multiple tables

Former Member — Sat, 09 Aug 2008 06:20:49 GMT

To perform selection from multiple tables, check for the common fields preferably key fields are required to establish the relationship.

You can achieve it by using Joins/for all entries.

Joins(Preferably Inner Joins) is really a good approach.

Check this query.

Here I am using two tables MKPF and MSEG.

MKPF(Material Document Header)

MSEG(Material Document Segment)

Both the tables contains the common fields MBLNR and GJAHR.

Now check the query.

select a_{mblnr a}gjahr b_{mblnr b}gjahr b_{matnr b}menge b_{meins from mkpf as a inner join mseg as b on a}mblnr = b~mblnr where mblnr in <select-options>

Now here you can place several conditions based on your program. "a" is called the alias name for MKPF table and "b" is called as the alias name for MSEG table.

Similarly you can establish the link with several tables. Now based on the material in MSEG table You can establish link to MARC(Plant data) etc.

It is very similar for adding the other tables in the same select query, but follow the rules of establishing links using joins.

Also follow the "Indexes" properly so that database performance is optimized.

Regards,

Santosh Kumar M.