Inner Join

[Former Member]

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

[Former Member]

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 p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

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.

[Former Member]

Hi ,

U can use like

SELECT a_{vbeln b}posnr INTO TABLE g_t_vbeln FROM vbep AS a

INNER JOIN lips AS b

ON a_{vbeln = b}vgbel

AND a_{posnr = b}vgpos.

Regards,

[Former Member]

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.

[Former Member]

Hi Vimal,

While selecting the data using inner join select all the key fields.

Regards,

Atish

[Former Member]

Hi,

select-options s_vbeln.

select vbep_{vbeln vbep}posnr lips_{vgbel lips}vgpos into corresponding fileds of table itab from ( vbep inner join lips ) on lips_{vbeln = vbep}vbeln where

vbep~vbeln = s_vbeln.

Regards,

Priyanka.

[Former Member]

Hi,

SELECT VBEP_{POSNR VBEP}VBELN LIPS_{POSNR LIPS}VBELN

INTO (VBEP-POSNR , VBEP-VBELN , LIPS-POSNR , LIPS-VBELN )

FROM ( VBEP

INNER JOIN LIPS

ON LIPS_{VGPOS = VBEP}POSNR

AND LIPS_{VGBEL = VBEP}VBELN )

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....

[mohammed_moqeeth]

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 vbep_{vbeln = lips}vbeln AND

vbep_{posnr = lips}posnr

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>