topic Re: Using inner join in Application Development and Automation Discussions

Using inner join

Former Member — Thu, 06 Jul 2006 19:24:02 GMT

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.

Re: Using inner join

Laxmana_Appana_ — Thu, 06 Jul 2006 19:28:26 GMT

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

Re: Using inner join

RichHeilman — Thu, 06 Jul 2006 19:29:12 GMT

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

Re: Using inner join

Former Member — Thu, 06 Jul 2006 19:30:55 GMT

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.

Re: Using inner join

Former Member — Thu, 06 Jul 2006 19:38:09 GMT

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

Re: Using inner join

Former Member — Thu, 06 Jul 2006 19:48:53 GMT

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.