topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Tue, 23 Dec 2008 09:34:56 GMT

hi all,

can any one plz teel me how to write the inner join for this query..? im trying but its giving me errors..

select budat belnr into (itab-budat,itab-belnr) from bkpf
where bukrs in bukrs and budat between  budat-low and budat-high .


 select bukrs hkont gsber shkzg dmbtr into (itab-bukrs,itab-hkont,
 itab-gsber,itab-shkzg,itab-dmbtr) from bseg
 where bukrs in bukrs and hkont in hkont and gsber in gsber
 and belnr = itab-belnr.


 if sy-subrc = 0.

   append itab.
   clear itab.

 endif.

 endselect.
endselect.

thanx..

<LOCKED BY MODERATOR - USE APPROPRIATE TITLES>

Edited by: Alvaro Tejada Galindo on Dec 23, 2008 10:31 AM

Re: inner join

Former Member — Tue, 23 Dec 2008 09:40:44 GMT

BSEG is a cluster table . You cannot have a join on it.

Re: inner join

Former Member — Tue, 23 Dec 2008 09:44:46 GMT

hi,

Sorry...

Edited by: avinash kodarapu on Dec 23, 2008 3:15 PM

Re: inner join

Former Member — Tue, 23 Dec 2008 09:51:12 GMT

so what is an alternative which and what kind of join on which table can be applied..

thanx...

Re: inner join

Former Member — Tue, 23 Dec 2008 09:51:23 GMT

Hi Nilesh,

As mentioned by Ankesh, You can't use cluster or pool tables as your join tables.

Please check the example JOINS. Use this on non cluster or pool tables.


SELECT s~carrid s~carrname p~connid 
       INTO CORRESPONDING FIELDS OF TABLE itab 
       FROM scarr AS s 
       INNER JOIN spfli AS p ON s~carrid   =  p~carrid 
                                  AND p~cityfrom = p_cityfr.

If you cannot perfor JOINS then use FOR ALL ENTRIES. Please check the below code.


SELECT BUDAT BELNR
  FROM BKPF
  INTO TABLE IT_BKPF
 WHERE bukrs in bukrs and budat between  budat-low and budat-high .

IF SY-SUBRC EQ 0.
 
 select bukrs hkont gsber shkzg dmbtr 
 from bseg
 INTO TABLE IT_BSEG
 where bukrs in bukrs and hkont in hkont and gsber in gsber
 and belnr = itab-belnr.
ENDIF.

Thanks,

Vinay

Re: inner join

Former Member — Tue, 23 Dec 2008 09:55:37 GMT

please ignore this;

Re: inner join

Former Member — Tue, 23 Dec 2008 10:04:24 GMT

select a~budat a~belnr 
b~bukrs b~hkont b~gsber b~shkzg b~dmbtr into 
(itab-budat,itab-belnr,itab-bukrs,itab-hkont,itab-gsber,itab-shkzg,
tdmbtr) 
from bkpf as a inner join  bseg as b on a~belnr = b~belnr
where budat between  budat-low and budat-high and bukrs in bukrs and 
bukrs in bukrs and hkont in hkont and gsber in gsber.

im doing like this m i syntactically right..?

thanx..

Re: inner join

Former Member — Tue, 23 Dec 2008 10:11:26 GMT

Hi Nilesh,

try it like this:

select bkpf~bukrs
         bkpf~budat
         bkpf~belnr
    into table t_bkpf
    from bkpf 
   where bukrs in s_bukrs
     and budat in s_budat.
     
  select hkont
         gsber
         shkzg
         dmbtr
    into table t_bseg
     for all entries in t_bkpf
   where bukrs eq t_bkpf-bukrs
     and gsber in s_gsber
     and hkont in s_hkont.

With luck,

Pritam.

Re: inner join

Former Member — Tue, 23 Dec 2008 11:20:47 GMT

HI,

AS BSEG is the cluster table you can't use the innerjoin for that.

you have to the use "For all entries" only

Note: while using the cluster table you need to use the all primary keys in the where condition other wise you wont' get the proper data.

for the BSEG you have to use the BUKRS,BELNR,GJAHR,BUZEI in the where condtion. with same sequance.

Regards,

Prasanth