topic Re: join two internal tables in Application Development and Automation Discussions

join two internal tables

Former Member — Thu, 28 Sep 2006 10:52:15 GMT

hi all

please give a query for joining of three internal tables and store into another table,and we should retrive data from that third table

please take those fields as material number,plant,storage location,stock,units

Re: join two internal tables

Former Member — Thu, 28 Sep 2006 10:57:23 GMT

Hello Raja,

SELECT EKKN_{EBELN EKKN}EBELP EKKN_{KOSTL EKKN}PS_PSP_PNR EKKN~SAKTO

EKET_{WEMNG EKET}ETENR EKET_{BEDAT EKPO}KNTTP EKPO_{LOEKZ EKPO}MATNR

EKPO_{WERKS EKPO}MATKL EKPO_{MENGE EKPO}MEINS EKPO_{NETPR EKPO}PEINH

EKPO_{NETWR EKPO}MTART EKPO_{EFFWR EKKO}BUKRS EKKO_{BSART EKKO}ERNAM

EKKO_{LIFNR EKKO}EKORG EKKO_{EKGRP EKKO}WAERS EKKO~BEDAT

EKKO~MEMORY INTO TABLE G_T_OUTTAB

FROM ( EKKN

INNER JOIN EKET

ON EKET_{EBELN = EKKN}EBELN

AND EKET_{EBELP = EKKN}EBELP

INNER JOIN EKPO

ON EKPO_{EBELN = EKET}EBELN

AND EKPO_{EBELP = EKET}EBELP

INNER JOIN EKKO

ON EKKO_{EBELN = EKPO}EBELN )

WHERE EKKN~EBELN IN S_EBELN

AND EKKN~EBELP IN S_EBELP

AND EKKN~KOSTL IN S_KOSTL

AND EKKN~PS_PSP_PNR IN S_WBS

AND EKKN~SAKTO IN S_SAKTO

AND EKPO~KNTTP IN S_KNTTP

AND EKPO~LOEKZ IN S_LOEKZ

AND EKPO~MATKL IN S_MATKL

AND EKPO~MATNR IN S_MATNR

AND EKPO~WERKS IN S_WERKS

AND EKKO~BEDAT IN S_BEDAT

AND EKKO~BSART IN S_BSART

AND EKKO~BUKRS IN S_BUKRS

AND EKKO~EKGRP IN S_EKGRP

AND EKKO~EKORG IN S_EKORG

AND EKKO~ERNAM IN S_ERNAM

AND EKKO~LIFNR IN S_LIFNR

AND EKPO~MTART IN S_MTART

AND EKKO~MEMORY IN S_MEMORY

AND EKET~BEDAT IN S_DAT_ET.

Try with this code.

If useful reward.

Vasanth

Re: join two internal tables

Former Member — Thu, 28 Sep 2006 11:08:55 GMT

Hi,

find this,

SELECT DISTINCT a_{herkunft a}matnr a_{prueflos a}charg

b_{vorglfnr b}merknr b_{verwmerkm b}kurztext

b_{sollwert b}toleranzun b_{toleranzob " c}probenr

b_{sollwni b}tolunni b~tolobni

FROM ( qals AS a INNER JOIN qamv AS b

ON a_{prueflos = b}prueflos )

INNER JOIN qapp AS c

ON b_{prueflos = c}prueflos

AND b_{vorglfnr = c}vorglfnr

INTO CORRESPONDING FIELDS OF itab

WHERE a~aufnr = order

AND b~verwmerkm IN mic_code

AND c~userc1 IN mc_no

AND c~userc2 IN mc_type

AND c~usern1 IN l_no

AND a~herkunft = '03'.

Re: join two internal tables

Former Member — Thu, 28 Sep 2006 11:19:05 GMT

Hi,

please take those fields as material

SELECT MARD_{WERKS MARD}LGORT MARD_{MATNR MARD}LABST

INTO TABLE IT_MARD

FROM MARD

INNER JOIN MARA

ON MARA_{MATNR = MARD}MATNR

WHERE MARD~MATNR IN S_MATNR AND

MARD~WERKS IN R_WERKS.

regards

Amole

Re: join two internal tables

Former Member — Thu, 28 Sep 2006 12:41:00 GMT

Hi Raja Gopal,

i got u r point,

let us consider

itab1 with a1 a2 a3 contains 100 records

itab2 with b1 b2 b3 contains 30 reocrds

itab3 with c1 c2 c3 contains 30 records

and a1 = b1 and b2 = c2

u want to fill itab4 with a1 a2 a3 b2 b3 c1 c3

now follow this

loop at itab1.

itab4-a1 = itab1-a1.

itab4-a2 = itab1-a2.

itab4-a3 = itab1-a3.

now read table itab2 by comparing a1 and b1 on these tables and append to itab4.

read table itab2 with key b1 = itab1-a1.

itab4-b2 = itab2-b2.

itab4-b3 = itab2-b3.

now read table itab3 by comparing b2 from retrieved itab2 and with c2 in itab3, on these tables and append to itab4.

read table itab3 with key c2 = itab2-b2.

itab4-c1 = itab3-c1.

itab4-c3 = itab3-c3.

append itab4.

endloop.

now in final internal table itab4 u will have all data from all internal tables.

NOTE: while joining two internal table.

use loop statement for one internal table. and use read statement for one intenaltable.

NOTE: while using read and loop.

Use LOOP for a table that has large no of records

Use READ for a table that has small no of records