topic Re: join query in Application Development and Automation Discussions

join query

Former Member — Thu, 04 Feb 2010 09:32:48 GMT

Hi all,

I want 2 join bkpf and ekbe table.

I use this relation

ekbe-belnr = bkpf-awkey+0(10).

but it cant work.

So, plz anybody can say me way for this.

Thanks in advance

Urvi

Re: join query

Former Member — Thu, 04 Feb 2010 10:06:25 GMT

Hi,

XBLNR is common field use this.

or use this

Ekbe-belnr = bkpf-belnr

plz check fields AWTYP and AWKEY

regards

Santosh

Re: join query

Former Member — Thu, 04 Feb 2010 11:16:48 GMT

Hi,

For both the tables you can use the join condition based on the field BELNR.

Hope this will help you.

Jatender

Re: join query

Former Member — Thu, 04 Feb 2010 12:25:35 GMT

Hi,

Get the records from the table BKPF and then use the awkey+0 and select data from EKBE using

belnr = it_bkpf-awkey+0(10)

Hope this helps you,

Regards,

Abhijit G. Borkar

Re: join query

Former Member — Thu, 04 Feb 2010 12:49:56 GMT

Hi,

Both the table have the field BELNR. So can compare with it.

Re: join query

christine_evans — Tue, 09 Feb 2010 14:32:45 GMT


> Hi,
> Both the table have the field BELNR.  So can compare with it.

But they're different BELNRs: BKPF contains the accounting document BELNR and EKBE the material document BELNR. So, you can't join them. To the OP, you can't use a SUBSTR with a JOIN in Open SQL, so you can't do what you want to do in a single SELECT; you'll need a couple of SELECTs and a FOR ALL ENTRIES.

Re: join query

Former Member — Tue, 09 Feb 2010 15:49:47 GMT

Hello,

Here is the codeyou need (if I understood what you need ;o)

DATA:

lt_ekbe TYPE TABLE OF ekbe,

ls_ekbe TYPE ekbe,

lt_bkpf TYPE TABLE OF bkpf,

ls_bkpf TYPE bkpf,

lv_belnr TYPE belnr,

lt_belnr TYPE TABLE OF belnr.

SELECT * INTO TABLE lt_bkpf

FROM bkpf

WHERE "Your condition".

LOOP AT lt_bkpf INTO ls_bkpf.

lv_belnr = ls_bkpf-awkey(10).

APPEND lv_belnr TO lt_belnr.

ENDLOOP.

SELECT * INTO TABLE lt_ekbe

FROM ekbe

FOR ALL ENTRIES IN lt_belnr

WHERE belnr = lt_belnr. "(Don't rememeber if you have to put lt_belnr -BELNR )

Hope this helps 🐵

KR,

Luis

Re: join query

Former Member — Tue, 02 Mar 2010 11:14:31 GMT

Hi,

In select statement try like below

Invoice details

IF Pt_account_document_head_table[] Is Not Initial.

Select Belnr Gjahr Lifnr

Budat Bldat Cpudt

Rmwwr Waers

Into table Pt_Invoice_Header_Table

From Rbkp

For All Entries in Pt_Account_Document_Head_Table

Where belnr Eq Pt_Account_Document_Head_Table-Object_Key(10) And

Belnr In s_Inum And

Cpudt In s_IentDt And

Bldat In s_IdocDt And

Budat In s_IposDt.

Endif.

Thanks & Regards

Ramakrishna P.

Re: join query

Former Member — Tue, 09 Mar 2010 11:37:29 GMT

The select query will not give the desired result if you are using the fields having different data type or field length in where caluse as you used for field BKPF-AWKEY. Instead of using the where condition as 'ekbe-belnr = bkpf-awkey+0(10)' directly , first fetch the data from bkpf table. Apeend the first ten chracters of field AWKEY in another internal table and use 'Sekect For ALL ENTRIES' for this internal table while fetching the data from EKBE table .

Hope this will solve your problem.