topic Re: Inner join problem in Application Development and Automation Discussions

Inner join problem

Former Member — Thu, 20 Nov 2008 17:57:33 GMT

Hi All,

I am writing the inner join for the tables LFA1 and LFB1. like this.

SELECT a_{lifnr a}ktokk a_{name1 a}sortl a~adrnr

b_{bukrs b}zterm

INTO CORRESPONDING FIELDS OF TABLE it_lfb1

FROM lfa1 AS a

INNER JOIN lfb1 AS b

ON b_{lifnr = a}lifnr

WHERE a_{sperr = ' ' AND a}loevm = ' '

AND a_{ktokk = 'ZZ03' OR a}ktokk = 'ZZ04' OR a~ktokk = 'ZZ05'

AND b~bukrs = p_cocode.

I am using the company code 4402

When iam getting the data 3 extra records are coming which are not avaliable in the company code 4402

<<text removed>>.

Edited by: Matt on Nov 21, 2008 11:57 AM

Re: Inner join problem

Former Member — Thu, 20 Nov 2008 18:12:20 GMT

Please read the help on using brackets in logical expressions.

Rob

Re: Inner join problem

Former Member — Thu, 20 Nov 2008 22:18:38 GMT

Hi,

Better use for all entries... its better for performance wise.


   select x y x from tab1 into it_tab1.
  
   select x m n from tab2 into it_tab2
          for all entries in it_tab1
          where x = it_tab1-x.

Thanks & Regards,

Krishna..

Re: Inner join problem

Former Member — Fri, 21 Nov 2008 02:54:15 GMT

Hi swapna,

Try this:-

SELECT a~lifnr

a~ktokk

a~name1

a~sortl

a~adrnr

b~bukrs

b~zterm

INTO CORRESPONDING FIELDS OF TABLE it_lfb1

FROM lfa1 AS a

INNER JOIN lfb1 AS b

ON b_{lifnr = a}lifnr

WHERE ( a_{sperr = ' ' AND a}loevm = ' ' )

AND( a_{ktokk = 'ZZ03' OR a}ktokk = 'ZZ04' OR a~ktokk = 'ZZ05' )

AND b~bukrs = p_cocode.

Hope this solves ur problem.

Thanks & Regards

Ruchi Tiwari

Re: Inner join problem

Former Member — Fri, 21 Nov 2008 03:25:19 GMT

Hi,

I think the problem occurs on the AND and OR statement:

SELECT a_{lifnr a}ktokk a_{name1 a}sortl a~adrnr

b_{bukrs b}zterm

INTO CORRESPONDING FIELDS OF TABLE it_lfb1

FROM lfa1 AS a

INNER JOIN lfb1 AS b

ON b_{lifnr = a}lifnr

WHERE a_{sperr = ' ' AND a}loevm = ' '

AND a_{ktokk = 'ZZ03' OR a}ktokk = 'ZZ04' OR a~ktokk = 'ZZ05'

" change to AND a~ktokk in ('ZZ03', 'ZZ04', 'ZZ05')

AND b~bukrs = p_cocode.

I think it can solve the problem.

Regards,

Chris Gu

Edited by: Gu Chris on Nov 21, 2008 4:25 AM

Re: Inner join problem

Former Member — Fri, 21 Nov 2008 10:32:53 GMT

Hi Ruchi,

Thank you so much for your solution.I rewarded you the points.

Thanks,

Swapna.