fetching Data from More than 2 tables

[Former Member]

Dear All,

I wanted to know, how can I write a Simple ABAP Program, where I have to fetch data from more than 2 tables. I know joining 2 tables but want should I do if I have to fetch data from 3 or more tables.

Any example would be really great.

Thanking you.

Regards

Venkat.

[Former Member]

Hi,

Check this example...

DATA: BEGIN OF ITAB OCCURS 0,

MATNR TYPE MATNR,

WERKS TYPE WERKS_D,

VKORG TYPE VKORG,

VTWEG TYPE VTWEG,

END OF ITAB.

PARAMETERS: P_MATNR TYPE MATNR.

SELECT A_{MATNR B}WERKS C~VKORG

C~VTWEG

INTO TABLE ITAB

FROM MARA AS A INNER JOIN MARC AS B

ON A_{MATNR = B}MATNR

INNER JOIN MVKE AS C

ON A_{MATNR = C}MATNR

WHERE A~MATNR = P_MATNR.

CHECK SY-SUBRC = 0.

Thanks,

Naren

[Former Member]

Hi venkat,

This is one select query which I worte it today so sending you.

Here I am fetching data from more than 3 tables.

<b>SELECT ST_{STLNR ST}IDNRK ST_{POSNR ST}MENGE ST~MEINS

MK~MAKTX

AK~AUFNR

INTO CORRESPONDING FIELDS OF TABLE IT_STPO

FROM STPO AS ST INNER JOIN MAKT AS MK

ON ST_{IDNRK = MK}MATNR

INNER JOIN AFKO AS AK

ON ST_{STLNR = AK}STLNR

FOR ALL ENTRIES IN IT_AFRU

WHERE ST~STLNR = IT_AFRU-STLNR AND

AK~AUFNR = IT_AFRU-AUFNR AND

SPRAS = 'E'.</b>

Let me know if you have any queries.

Vinod.

[Former Member]

Hi Vinod / Narender,

Thank you very much for your replies and examples, I tried them and felt happy to see the results.

Thank you once again, I am alloting points for your efforts.

Can you also let me know, what if I have to fetch data from more then 3 tables.

Thanking you.

Regards

Venkat

[Former Member]

Hi Naren,

This is an example for fetching data from 3 tables.

Here I am getting data from

MKPF, MSEG, AFPO, MAKT, and AFRU

SELECT MK_{MBLNR MK}BUDAT

MS_{AUFNR MS}BWART MS_{ERFMG MS}ERFME

AF~MATNR

MD~MAKTX

INTO CORRESPONDING FIELDS OF TABLE IT_MKPF_FIR

FROM MKPF AS MK INNER JOIN MSEG AS MS

ON MK_{MBLNR = MS}MBLNR

INNER JOIN AFPO AS AF

ON MS_{AUFNR = AF}AUFNR AND

AF_{MATNR = MS}MATNR

INNER JOIN MAKT AS MD

ON AF_{MATNR = MD}MATNR

FOR ALL ENTRIES IN IT_AFRU

WHERE MK~BUDAT = IT_AFRU-BUDAT AND

MS~AUFNR = IT_AFRU-AUFNR AND

MD~SPRAS = 'E'.

Vinod.

<b>Allot points for Valid answers</b>

[Former Member]

Hi,

Check this example for more than 3 tables...

DATA: BEGIN OF ITAB OCCURS 0,

MATNR TYPE MATNR,

WERKS TYPE WERKS_D,

VKORG TYPE VKORG,

VTWEG TYPE VTWEG,

BWKEY TYPE BWKEY,

END OF ITAB.

PARAMETERS: P_MATNR TYPE MATNR.

SELECT A_{MATNR B}WERKS C~VKORG

C_{VTWEG D}BWKEY

INTO TABLE ITAB

FROM MARA AS A

INNER JOIN MARC AS B

ON A_{MATNR = B}MATNR

INNER JOIN MVKE AS C

ON A_{MATNR = C}MATNR

INNER JOIN MBEW AS D

ON A_{MATNR = D}MATNR

WHERE A~MATNR = P_MATNR.

CHECK SY-SUBRC = 0.

Thanks,

Naren

[Former Member]

Hi Vinod / Naren,

Thank you once again. Your inputs are really very helpful to me. I am Thankful to you guys.

Please don"t mind, I have other question. I read some where that you should not join more than 3 tables due to performance issues.

I belive we will write select statements for 2 times, or use read statement.

Please let me know about this.

Thanks & Regards

Venkat

[Former Member]

It is not advisable to join more than three tables but it completely depends on your join and the join conditions..if u are using all primary keys for join etc than it is worthwhile but if not than it is better to use For all enteries in the another select statement.

[Former Member]

Hi Venkat,

U can join multiple tables no prob but performance will be a major issue ...so joining upto 2 tables is ok but for more than 2 u may experience problem. Retrieve all the records by joining two tables in one internal table then using that table as a driver table try to select rest of the records from other tables .

If it sounds good pl reward.

Cheers.

[Former Member]

Hi,

You can join multiple tables no prob but performance will be a major issue ...so joining upto 3 tables is ok but for more than 3 you may experience problem. Retrieve all the records by joining three tables in one internal table then using that internal table in the command Select for all entries try to get the records from other table.

Regadrs,

Govindarajan Umadevi.

[Former Member]

SELECT MSEG_{MATNR MSEG}WERKS MSEG_{MBLNR MSEG}ZEILE MSEG~EBELN

MSEG_{EBELP MSEG}MENGE MSEG_{BWART MSEG}EBELN MSEG~ERFME

MSEG_{KOSTL MSEG}LGORT MSEG_{MJAHR MSEG}SHKZG EKKO~EKGRP

EKKO_{LIFNR EKPO}MATKL MKPF_{BUDAT MKPF}USNAM

FROM ( MSEG

INNER JOIN EKKO

ON MSEG_{EBELN = EKKO}EBELN

INNER JOIN EKPO

ON MSEG_{EBELN = EKPO}EBELN

AND MSEG_{EBELP = EKPO}EBELP

INNER JOIN MKPF

ON MSEG_{MBLNR = MKPF}MBLNR

AND MSEG_{MJAHR = MKPF}MJAHR )

APPENDING CORRESPONDING FIELDS OF TABLE IT_YMM_ST_TNUOT_MLY

WHERE MSEG~MBLNR IN SL_MBLNR

AND MSEG~MATNR IN SL_MATNR

AND MSEG~WERKS IN SL_WERKS

AND MSEG~LGORT IN SL_LGORT

AND MSEG~LIFNR IN SL_LIFNR

AND MSEG~KOSTL IN SL_KOSTL

AND EKKO~EKGRP IN SL_EKGRP

AND EKPO~MATKL IN SL_MATKL

AND MKPF~BUDAT IN SL_BUDAT .