04-01-2008 7:59 AM
hello all,
how can i join three tables through inner join.
give an example
thanks.
04-01-2008 8:03 AM
Hi,
* Types
TYPES: BEGIN OF ty_target,
ekko_ebeln TYPE ekko-ebeln,
ekko_bukrs TYPE ekko-bukrs,
ekko_bstyp TYPE ekko-bstyp,
bsart TYPE ekko-bsart,
ekpo_ebeln TYPE ekpo-ebeln,
ekpo_ebelp TYPE ekpo-ebelp,
ekpo_aedat TYPE ekpo-aedat,
txz01 TYPE ekpo-txz01,
etenr TYPE eket-etenr,
eindt TYPE eket-eindt,
eket_menge TYPE eket-menge,
ameng TYPE eket-ameng,
END OF ty_target.
* Internal tables and work areas
DATA:
it_data TYPE TABLE OF ty_target,
wa_data LIKE LINE OF it_data.
SELECTION-SCREEN: BEGIN OF BLOCK b01 WITH FRAME TITLE title01.
PARAMETERS: p_bukrs TYPE bukrs.
SELECT-OPTIONS: so_ebeln FOR ekko-ebeln.
SELECTION-SCREEN: END OF BLOCK b01.
SELECT a~ebeln a~bukrs a~bstyp a~bsart b~ebeln b~ebelp b~aedat b~txz01 c~etenr
c~eindt c~menge c~ameng
FROM ( ( ekko AS a
INNER JOIN ekpo AS b ON a~ebeln = b~ebeln AND
a~lponr = b~ebelp )
INNER JOIN eket AS c ON b~ebeln = c~ebeln AND
b~ebelp = c~ebelp )
INTO TABLE it_data " <-- Attention ! Field sequence !
WHERE a~ebeln IN so_ebeln AND
a~bukrs EQ p_bukrs .
Regards
Sandipan
Edited by: Sandipan Ghosh on Apr 1, 2008 12:33 PM
04-01-2008 8:09 AM
Hi
If the tables are like header and item as well as inter related like VBAK VBAP VBPA etc
then it would be more faster than for all entries method
check this code
SELECT stpostlnr stpoidnrk mastmatnr maramtart stpo~menge
INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast
JOIN stpo ON stpostlnr = maststlnr
JOIN mara ON maramatnr = mastmatnr
WHERE stpostlty = 'M' "AND stpoidnrk IN s_matnr
AND mast~werks = 1000.
Here s_matnr is a select-options on the selection-screen.
Or this.
Code:
Select single VbrkBukrs VbrkKunrg Vbrk~Vbeln
VbrkFkdat VbrkBstnk_Vf Vbrk~Zterm
Tvzbt~Vtext
VbakVbeln VbakBstdk
LikpVbeln Likplfdat Likp~Lfuhr
into w_vbrk
from vbrk
inner join Tvzbt on TvzbtZterm = VbrkZterm and
Tvzbt~Spras = sy-langu
Inner join Vbfa as SalesLnk
on SalesLnk~vbeln = pu_vbeln and
SalesLnk~vbtyp_v = c_order
inner join Vbak on VbakVbeln = SalesLnkVbelv
Inner join Vbfa as DeliveryLnk
on DeliveryLnk~vbeln = pu_vbeln and
DeliveryLnk~vbtyp_v = c_Delivery
inner join Likp on LikpVbeln = DeliveryLnkVbelv
where vbrk~vbeln = pu_Vbeln.
regards
Shiva
04-01-2008 8:14 AM
Hi,
Check the following select statement:
select zfpcdcadivi zfpcdproforma zfpcdfactura zfpcdaniofactura
zfpcdmontousd zfpcdmontoap zfpcdebeln zfpcdinco1
zfpcdlifnr lfa1name1 zcdvsstatus zfpcdconint
into it_lista
from zfpcd inner join zcdvs
on zfpcdebeln = zcdvsebeln
and zfpcdproforma = zcdvsproforma
and zfpcdlifnr = zcdvslifnr
inner join lfa1
on zfpcdlifnr = lfa1lifnr
where zcdvs~status = '04'.
Regards,
Bhaskar
04-01-2008 8:28 AM
Hi,
Check this....
REPORT YPRO_4 LINE-SIZE 150. .
TABLES: VBAP, LIPS, VBRP.
SELECT-OPTIONS: A_VBELN FOR VBAP-VBELN.
DATA: BEGIN OF IT OCCURS 0,
VBELN LIKE VBAP-VBELN,
POSNR LIKE VBAP-POSNR,
KWMENG LIKE VBAP-KWMENG,
L_VBELN LIKE LIPS-VBELN,
L_POSNR LIKE LIPS-POSNR,
LFIMG LIKE LIPS-LFIMG,
R_VBELN LIKE VBRP-VBELN,
R_POSNR LIKE VBRP-POSNR,
FKIMG LIKE VBRP-FKIMG,
END OF IT.
SELECT VBAP~VBELN VBAP~POSNR VBAP~KWMENG
LIPS~VBELN LIPS~POSNR LIPS~LFIMG
VBRP~VBELN VBRP~POSNR VBRP~FKIMG
INTO TABLE IT FROM LIPS INNER JOIN VBAP
ON VBAP~VBELN = LIPS~VGBEL AND
VBAP~POSNR = LIPS~VGPOS
INNER JOIN VBRP ON
LIPS~VBELN = VBRP~VGBEL AND
LIPS~POSNR = VBRP~VGPOS
WHERE VBAP~VBELN
IN A_VBELN.
LOOP AT IT.
FORMAT INVERSE ON.
FORMAT COLOR 6.
WRITE:/ SY-VLINE, IT-VBELN,
SY-VLINE, IT-POSNR,
SY-VLINE, IT-KWMENG,
SY-VLINE, IT-L_VBELN,
SY-VLINE, IT-L_POSNR,
SY-VLINE, IT-LFIMG,
SY-VLINE, IT-R_VBELN,
SY-VLINE, IT-R_POSNR,
SY-VLINE, IT-FKIMG,
SY-VLINE.
WRITE:/ SY-ULINE(129).
FORMAT INVERSE OFF.
FORMAT RESET.
ENDLOOP.
Reward points if useful....
Regards
AK