problem in join statement

[Former Member]

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

[jayanthi_jayaraman]

Hi,

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

Otherwise,check the sample code using F1.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

hi

can u send the select statement which u written.

[Former Member]

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

[Former Member]

hi

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

so that it can improve performace

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

hi

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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