topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Wed, 28 May 2008 11:03:33 GMT

hi,

how can we create inner join using these fields of table

TYPES: BEGIN OF it_output,

bukrs TYPE bseg-bukrs,

belnr TYPE bseg-belnr,

gjahr TYPE bseg-gjahr,

fisper TYPE bseg-gjahr,

buzei TYPE bseg-buzei,

augdt TYPE bseg-augdt,

shkzg TYPE bseg-shkzg,

dmbtr TYPE bseg-dmbtr,

kostl TYPE bseg-kostl,

hkont TYPE bseg-hkont,

matnr TYPE bseg-matnr,

werks TYPE bseg-werks,

blart TYPE bkpf-blart,

bldat TYPE bkpf-bldat,

budat TYPE bkpf-budat,

cpudt TYPE bkpf-cpudt,

usnam TYPE bkpf-usnam,

tcode TYPE bkpf-tcode,

bktxt TYPE bkpf-bktxt,

waers TYPE bkpf-waers,

awtyp TYPE bkpf-awtyp,

awkey TYPE bkpf-awkey,

END OF it_output.

SELECT-OPTIONS: s_bukrs FOR bseg-bukrs,

s_blart FOR bkpf-blart,

s_budat FOR bkpf-budat.

thanks in advance

Re: inner join

Former Member — Wed, 28 May 2008 11:10:17 GMT

You cant use joins on cluster table so find alternative table and

You can modify this one



SELECT stpo~stlnr stpo~idnrk mast~matnr mara~mtart stpo~menge  
INTO TABLE zmat1 FROM mast  
JOIN stpo ON stpo~stlnr = mast~stlnr  
JOIN mara ON mara~matnr = mast~matnr  
WHERE  stpo~idnrk IN s_matnr  .

Re: inner join

Former Member — Wed, 28 May 2008 11:10:47 GMT

Hi,

u cannot use joins for this internal table because this table needs to get the values from BSEG and BSEG is a clustered table.

u cannot use join conditions on cluster tables.

so, first fill the table from BKPF.and the use for all entries to get the values from BSEG.

rgds,

bharat.

Re: inner join

Former Member — Wed, 28 May 2008 11:24:39 GMT

check this code

SELECT-OPTIONS: s_bukrs FOR bseg-bukrs,

s_blart FOR bkpf-blart,

s_budat FOR bkpf-budat.

TYPES: BEGIN OF it_output,

bukrs TYPE bseg-bukrs,

belnr TYPE bseg-belnr,

gjahr TYPE bseg-gjahr,

fisper TYPE bseg-gjahr,

buzei TYPE bseg-buzei,

augdt TYPE bseg-augdt,

shkzg TYPE bseg-shkzg,

dmbtr TYPE bseg-dmbtr,

kostl TYPE bseg-kostl,

hkont TYPE bseg-hkont,

matnr TYPE bseg-matnr,

werks TYPE bseg-werks,

blart TYPE bkpf-blart,

bldat TYPE bkpf-bldat,

budat TYPE bkpf-budat,

cpudt TYPE bkpf-cpudt,

usnam TYPE bkpf-usnam,

tcode TYPE bkpf-tcode,

bktxt TYPE bkpf-bktxt,

waers TYPE bkpf-waers,

awtyp TYPE bkpf-awtyp,

awkey TYPE bkpf-awkey,

END OF it_output.

select bseg~bukrs

bseg~belnr

bseg~gjahr

bseg~buzei

bseg~augdt

bseg~shkzg

bseg~dmbtr

bseg~kostl

bseg~hkont

bseg~matnr

bseg~werks

bkpf~blart

bkpf~bldat

bkpf~budat

bkpf~cpudt

bkpf~usnam

bkpf~tcode

bkpf~bktxt

bkpf~waers

bkpf~awtyp

bkpf~awkey

into table it_output

from bseg innerjoin bkpf

on bseg_{belnr = bkpf}belnr

where bseg~bukrs in s_bukrs

and bkpf~blart in s_blart

and bkpf~budat in s_budat.

thanks

sitaram

Re: inner join

vinod_vemuru2 — Wed, 28 May 2008 11:57:44 GMT

Hi Jayant,

U cant use JOIN here because BSEG is a cluster table. Joins can be done only between transparent tables.

U have to use FOR ALL ENTRIES here.

Copy-Paste below piece of code.


TYPES: BEGIN OF t_bkpf,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
blart TYPE bkpf-blart,
bldat TYPE bkpf-bldat,
budat TYPE bkpf-budat,
cpudt TYPE bkpf-cpudt,
usnam TYPE bkpf-usnam,
tcode TYPE bkpf-tcode,
bktxt TYPE bkpf-bktxt,
waers TYPE bkpf-waers,
awtyp TYPE bkpf-awtyp,
awkey TYPE bkpf-awkey,
END OF it_bkpf,

BEGIN OF t_bseg,
bukrs TYPE bseg-bukrs,
belnr TYPE bseg-belnr,
gjahr TYPE bseg-gjahr,
fisper TYPE bseg-gjahr,
buzei TYPE bseg-buzei,
augdt TYPE bseg-augdt,
shkzg TYPE bseg-shkzg,
dmbtr TYPE bseg-dmbtr,
kostl TYPE bseg-kostl,
hkont TYPE bseg-hkont,
matnr TYPE bseg-matnr,
werks TYPE bseg-werks,
END OF t_bseg.

DATA: i_bkpf TYPE STANDARD TABLE OF t_bkpf,
          i_bseg TYPE STANDARD TABLE OF t_bseg.

SELECT-OPTIONS: s_bukrs FOR bseg-bukrs,
s_blart FOR bkpf-blart,
s_budat FOR bkpf-budat.

SELECT bukrs belnr  gjahr blart bldat budat cpudt  usnam          tcode bktxt 
             waers awtyp awkey
            INTO TABLE i_bkpf
            FROM bkpf
            WHERE bukrs IN s_bukrs
            AND  blart IN s_blart
            AND  budat IN s_budat.

IF NOT i_bkpf[] IS INITIAL.
SORT i_bkpf BY bukrs belnr gjahr.
SELECT bukrs belnr  gjahr fisper buzei augdt shkzg 
             dmbtr kostl hkont matnr werks
              INTO TABLE i_bseg
              FROM bseg
              FOR ALL ENTRIES IN i_bkpf
              WHERE bukrs EQ i_bkpf-bukrs
              AND      belnr EQ i_bkpf-belnr
             AND      gjahr EQ i_bkpf-gjahr.
ENDIF.

*But these selects will be very very slow as we are not passing*
*all the key fields. Also check the order of fields in select wrt* *data base table. It should be in same order*

Thanks,

Vinod.