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

problem in join statement

Former Member — Wed, 10 Jan 2007 09:31:39 GMT

can any one tell how to join bkpf ,rbkp ,ekbe,ekbz

by using bkpf_{belnr = rbkp}belnr

bkpf_{bukrs = rbkp}bukrs

rbkp_{belnr = ekbe}belnr

rbkp_{benr = ekbz}belnr

Re: problem in join statement

jayanthi_jayaraman — Wed, 10 Jan 2007 09:36:53 GMT

Hi,

You cannot join the tables if it is pool or cluster.Just check the table attributes.

Otherwise,check the sample code using F1.

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 09:38:34 GMT

SELECT * 
  INTO TABLE i_output
  FROM bkpf as a 
  INNER JOIN rbkp as B
 ON a~belnr = b~belnr
 AND a~gkahr = b~gjahr
 INNER JOIN ekbe as c
 ON c~belnr = b~belnr
 AND c~gkahr = b~gjahr
INNER JOIN ekbz as d
ON d~belnr = c~belnr
 AND d~gkahr = c~gjahr
WHERE ...............................

This depends on the requirement.

U may have to use LEFT OUTER JOIN also.

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 09:39:38 GMT

can u send the select statement which u written.

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 09:40:25 GMT

bettter go for a view or do like this.

select a_{belnr b}bukrs c_{,f1> d}f2 into corresponding fields of table itab from rbkp as a inner join bkpf as b on a_{belnr = b}belnr and a_{bukrs = b}bukrs

inner join ekbe as c on a_{belnr = c}belnr

inner join ekbz as d on a_{belnr = d}belnr where<condn>.

performance will reduce for this code.

regards

shiba dutta

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 09:44:00 GMT

better to use for all entries concept, instead of using inner join for 4 tables..

so that it can improve performace

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 09:44:30 GMT

arun,

see the code.Inner joins are not advisable .

SELECT BKPF_{BELNR BKPF}BUKRS BKPF_{GJAHR RBKP}BELNR RBKP~BUKRS

RBKP_{GJAHR EKBE}BELNR EKBE_{BUZEI EKBE}EBELN EKBE~EBELP

EKBE_{GJAHR EKBE}VGABE EKBZ_{BELNR EKBZ}BUZEI EKBZ~EBELN

EKBZ_{EBELP EKBZ}GJAHR EKBZ~VGABE

INTO (BKPF-BELNR , BKPF-BUKRS , BKPF-GJAHR , RBKP-BELNR , RBKP-BUKRS

, RBKP-GJAHR , EKBE-BELNR , EKBE-BUZEI , EKBE-EBELN , EKBE-EBELP

, EKBE-GJAHR , EKBE-VGABE , EKBZ-BELNR , EKBZ-BUZEI , EKBZ-EBELN

, EKBZ-EBELP , EKBZ-GJAHR , EKBZ-VGABE )

FROM ( BKPF

INNER JOIN RBKP

ON RBKP_{BELNR = BKPF}BELNR

AND RBKP_{BUKRS = BKPF}BUKRS

AND RBKP_{GJAHR = BKPF}GJAHR

INNER JOIN EKBE

ON EKBE_{BELNR = RBKP}BELNR

AND EKBE_{GJAHR = RBKP}GJAHR

INNER JOIN EKBZ

ON EKBZ_{BELNR = EKBE}BELNR

AND EKBZ_{BUZEI = EKBE}BUZEI

AND EKBZ_{EBELN = EKBE}EBELN

AND EKBZ_{EBELP = EKBE}EBELP

AND EKBZ_{GJAHR = EKBE}GJAHR

AND EKBZ_{VGABE = EKBE}VGABE ).

Pls. reward.

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 09:55:33 GMT

hi murali thank u but the thing is i want to give the condition like this

and i want to print all the data contained in ekbe bkpf ekbz and rbkp

please check and rectify my mistake in this code

SELECT *

INTO TABLE i_main

FROM bkpf as a

INNER JOIN rbkp as B

ON a_{xblnr = b}xblnr

AND a_{gjahr = b}gjahr and

a_{budat = b}budat

INNER JOIN ekbe as c

ON c_{belnr = b}belnr

AND c_{gjahr = b}gjahr

INNER JOIN ekbz as d

ON d_{belnr = c}belnr

AND d_{gjahr = c}gjahr

WHERE a_{bukrs in bukrs and a}budat in budat and a_{blart in ('RD' , 'RE') AND C}BEWTP = 'Q'

AND D~BEWTP = 'M'.

LOOP AT I_MAIN.

WRITE:/I_MAIN-BELNR.

ENDLOOP.

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:05:37 GMT

please tell can u give me the code for this

the problem is is ekbe and ekbz may have multiple entries

please can u give me the code for this

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:07:13 GMT

can u please tell me how to write those fields inside the loop

for eg: rbkp also contain belnr

bkpf also contains belnr

i want to write bothe rbkp and bkpf belnr how to write in loop

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:16:03 GMT

first of all i have to know how did you declare the internal table.

because if you use include structure means the field repetation must be there.

if you are using one by one means in your select query you cannot use *.

correct me if i am wrong.

regards

shiba dutta

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:25:20 GMT

thank u shiba for ur reply

the thing is ur correct

i will tell my requirement

i want to fetch bkpf and rbkp fields by matching budat and xblnr and bukrs

then wtih the use of rbkpf~belnr i want to fetch all details from ekbe and ekbz

ekbe and ekbz may contain multiple entries

this is the requirement

can u please give me the code for this

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:30:49 GMT

my answer is just ommit * from the select query and give the fields name you required.

in your itab also define the field name.

in inner join only the matching values will come dont worry.

suppose in bkpf 1 belnr is there but in ekbe same 10 means it will fetch 10 data with same belnr no.

so try to do this tedious job and check whether your query is running or not.

if it is not working then reply with the error.

regards

shiba dutta

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:36:47 GMT

i have included wat u told but the output is not coming its not fetching properly

can u please tell me the exact code according to this condition,if u know to use for all entries also it will b better

but please give me the code

*SELECT a_{belnr c}ebeln

INTO TABLE i_main
FROM bkpf as a
INNER JOIN rbkp as B
ON a_{xblnr = b}xblnr
AND a_{gjahr = b}gjahr and
a_{budat = b}budat
INNER JOIN ekbe as c
ON c_{belnr = b}belnr
AND c_{gjahr = b}gjahr

*INNER JOIN ekbz as d

*ON d_{belnr = c}belnr

AND d_{gjahr = c}gjahr

*WHERE a_{bukrs in s_bukrs and a}budat in s_budat and a_{blart in ('RD' , 'RE') AND C}BEWTP = 'Q'.

**or D~BEWTP = 'M'.

*LOOP AT I_MAIN.

*WRITE:/02 I_MAIN-BELNR,10 i_main-ebeln.

*ENDLOOP.

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 10:59:35 GMT

i hink you are not using the joins between ekbe and ekbz

SELECT a_{belnr c}ebeln

INTO TABLE i_main
FROM bkpf as a
INNER JOIN rbkp as B
ON a_{xblnr = b}xblnr
AND a_{gjahr = b}gjahr and
a_{budat = b}budat
INNER JOIN ekbe as c
ON c_{belnr = b}belnr
AND c_{gjahr = b}gjahr

*INNER JOIN ekbz as d

*ON d_{belnr = c}belnr

AND d_{gjahr = c}gjahr

<b>and c_{ebeln = d}ebeln and

c_{ebelp = d}ebelp</b>*WHERE a_{bukrs in s_bukrs and a}budat in s_budat and a_{blart in ('RD' , 'RE') AND C}BEWTP = 'Q'.

**or D~BEWTP = 'M'.

try to join those two tables by primary key fields

if it also not works means let us check some other things.

regards

shiba dutta

Re: problem in join statement

Former Member — Wed, 10 Jan 2007 16:58:17 GMT

Hi,

Writing a Join for 4 tables is not advisable, it will become a mojor performace issue. try writing 2 joins and then write a logic to populate in a single table.

Regards,

Aravind