07-03-2007 4:25 AM
Hi,
I need to join two tables VBEP and LIPS using fields VBELN , POSNR in VBEP and
VGBEL and VGPOS from LIPS
Help me to sort out the problem.
Regards,
Vimal
07-03-2007 4:30 AM
HI,
see the example for inner join.
REPORT demo_select_inner_join.
DATA: BEGIN OF wa,
carrid TYPE spfli-carrid,
connid TYPE spfli-connid,
fldate TYPE sflight-fldate,
bookid TYPE sbook-bookid,
END OF wa,
itab LIKE SORTED TABLE OF wa
WITH UNIQUE KEY carrid connid fldate bookid.
SELECT pcarrid pconnid ffldate bbookid
INTO CORRESPONDING FIELDS OF TABLE itab
FROM ( ( spfli AS p
INNER JOIN sflight AS f ON pcarrid = fcarrid AND
pconnid = fconnid )
INNER JOIN sbook AS b ON bcarrid = fcarrid AND
bconnid = fconnid AND
bfldate = ffldate )
WHERE p~cityfrom = 'FRANKFURT' AND
p~cityto = 'NEW YORK' AND
fseatsmax > fseatsocc.
LOOP AT itab INTO wa.
AT NEW fldate.
WRITE: / wa-carrid, wa-connid, wa-fldate.
ENDAT.
WRITE / wa-bookid.
ENDLOOP.
<b>reward all helpful answers.</b>
rgds,
bharat.
07-03-2007 4:32 AM
Hi ,
U can use like
SELECT avbeln bposnr INTO TABLE g_t_vbeln FROM vbep AS a
INNER JOIN lips AS b
ON avbeln = bvgbel
AND aposnr = bvgpos.
Regards,
07-03-2007 4:42 AM
The same query i did but i have a problem i.e.
1) in VBEP a sale order all items are scheduled .
VBELN POSNR ETENR SchduledQty
eg) 10001 10 1 2
10001 10 2 3
10001 20 1 4
2) LIPS table. Looking for Delivered Quantity for the Sale order.
eg) VGBEL VGPOS LFIMG
10001 10 5
10001 20 4
while Using that join i get the data filled into Internal Table is,
10001 10
10001 10
10001 10
10001 10
10001 20 This is not Correct.
07-03-2007 4:43 AM
Hi Vimal,
While selecting the data using inner join select all the key fields.
Regards,
Atish
07-03-2007 4:33 AM
Hi,
select-options s_vbeln.
select vbepvbeln vbepposnr lipsvgbel lipsvgpos into corresponding fileds of table itab from ( vbep inner join lips ) on lipsvbeln = vbepvbeln where
vbep~vbeln = s_vbeln.
Regards,
Priyanka.
07-03-2007 4:38 AM
Hi,
SELECT VBEPPOSNR VBEPVBELN LIPSPOSNR LIPSVBELN
INTO (VBEP-POSNR , VBEP-VBELN , LIPS-POSNR , LIPS-VBELN )
FROM ( VBEP
INNER JOIN LIPS
ON LIPSVGPOS = VBEPPOSNR
AND LIPSVGBEL = VBEPVBELN )
WHERE your conditions here
Instead of INTO (VBEP-POSNR , VBEP-VBELN , LIPS-POSNR , LIPS-VBELN )
you acn use internal table for moving your selected fields....
Don't forget to reward if useful....
07-03-2007 4:42 AM
Hi Vimal,
<b>Please find the below code for joining VBEP & LIPS:</b>
<b>TABLES: vbep, lips.</b>
DATA: BEGIN OF itab OCCURS 0,
vbeln TYPE vbeln,
posnr TYPE posnr,
vgbel TYPE vgbel,
vgpos TYPE vgpos,
END OF itab.
SELECT-OPTIONS: s_vbeln FOR vbep-vbeln.
SELECT vbep~vbeln
vbep~posnr
lips~vgbel
lips~vgpos
INTO CORRESPONDING FIELDS OF TABLE itab
FROM <b>vbep</b> <b>INNER JOIN lips</b>
ON vbepvbeln = lipsvbeln AND
vbepposnr = lipsposnr
WHERE vbep~vbeln IN s_vbeln.
LOOP AT itab.
WRITE:/ itab-vbeln,
itab-posnr,
itab-vgbel,
itab-vgpos.
ENDLOOP.
<b>Reward Points.. if you feel helpful.
Cheers !
Moqeeth.</b>