Using inner join

[Former Member]

How can I write following using inner join instead of writing diffrently ?

Select VBELV POSNV from VBFA into V1 V2

WHERE VBELN = DELIVERY_ITEM-VBELN

AND POSNN = DELIVERY_ITEM-POSNR

AND VB_TYPE_N = 'J'.

Select POSEX from VBAP into V3.

where VBELN = V1

AND POSNR = V2

WRITE:/ V3.

[Laxmana_Appana_]

hi,

Check this

Select VBELV POSNV POSEX

into v1 v2 v3

from

VBFA as a inner join VBAP as b

on a_{VBELN = b}VBELN and

a_{POSNN = b}POSNR

where

a~POSNN = DELIVERY_ITEM-POSNR

AND a~VB_TYPE_N = 'J'.

laxman

[RichHeilman]

Try this.

select single vbfa~vbelv vbfa~posnv vbap~posex into (v1, v2, v3)
           from vbfa
               inner join vbap
                  on vbfa~vbelv = vbap~vbeln
                 and vbfa~posnv = vbap~posnr
                             where vbfa~vbeln = delivery_item-vbeln
                               and vbfa~posnn = delivery_item-posnr
                               and vbfa~vb_type_n = 'J'.
       

Regards,

Rich Heilman

[Former Member]

Hi Tushar,

try this way...

Select VBFA~VBELV

VBFA~POSNV

VBAP~POSEX

from VBFA

INNER JOIN VBAP

ON VBFA_{VBELV = VBAP}VBELN

VBFA_{POSNV = VBAP}POSNR

into V1 V2 V3

WHERE VBFA~VBELN = DELIVERY_ITEM-VBELN

AND VBFA~POSNN = DELIVERY_ITEM-POSNR

AND VB_TYPE_N = 'J'.

WRITE:/ V3.

[Former Member]

I think you are going about this the hard way. You can get the data you need from LIPS. Going to VBFA without the first elements in the primary key will be very expensive.

Rob

[Former Member]

Can you please close if answered?

For your current issue, I don't even go to VBFA. Don't you already have the sales order number and the item number in VGBEL, VGPOS fields of LIPS? If not check VBELV and POSNV fields. VBFA is known for perfomance issues. So try to avoid it as much as possible.