topic Re: need help with join in Application Development and Automation Discussions

need help with join

Former Member — Fri, 01 Jun 2007 14:08:06 GMT

Extract each entry from BSEG for BELNRs in BKPF-BELNR,

and KOSTL in ZTABLE-KOSTL.

sum(DMBTR) by KOSTL into

internal tab <ITAB (fields KOSTL, DMBTR)>.

i wrote the code like this:

Select sum( a_{DMBTR ) a}KOSTL into inkostltotal from BSEG as a

inner join BKPF as b on b_{BELNR = a}BELNR

inner join Ztable as c on c~KOSTL = a-KOSTL

group by a~KOSTL.

it was giving error:

You cannot use comparisons with column column references in pool and

cluster tables: "A~BELNR". column references in pool and cluster

tables: "A~BELNR". column references in pool and cluster tables:

"A_{BELNR". column tables: "A}BELNR".

i need to use select, sum, group by to solve this can some one

help me with this.

Re: need help with join

Former Member — Fri, 01 Jun 2007 14:12:28 GMT

Hi,

You can not use Inner Join on cluster tables. You need to find an alternate table or retrieve the records from cluster table into internal table and use FOR ALL ENTRIES clause in select query.

Regards,

Ferry Lianto

Re: need help with join

Former Member — Fri, 01 Jun 2007 14:14:21 GMT

try otherwise left outer join instead of inner join

Re: need help with join

Former Member — Fri, 01 Jun 2007 14:23:35 GMT

Hi Saritha,

You cannot use JOIN statement for a cluster table (BSEG).

Try to extract the entries from BKPF into an internal table (say i_bkpf), and form another SELECT statement for BSEG using entries in i_bkpf with FOR ALL ENTRIES IN.

Hope it helps.

Regards,

Patrick

Re: need help with join

Former Member — Fri, 01 Jun 2007 14:23:36 GMT

hi saritha,

y ur not using for all entries, that will best in this situation.

use for all entries like that

CLEAR : T_EQUZ,T_EQUZ[].

SELECT * FROM EQUZ INTO TABLE T_EQUZ

WHERE DATBI = '99991231'.

IF NOT T_EQUZ[] IS INITIAL.

select J_3GBELNRI equnr J_3GEMPFAE into

corresponding fields of table

T_J_3GBELP from J_3GBELP

FOR ALL ENTRIES IN T_EQUZ

where j_3guposnr ne '1'

and J_3GEMPFAE in Reciver

and equnr = T_EQUZ-EQUNR.

regards

vijay dwivedi

rewards if usefull

Re: need help with join

Former Member — Fri, 01 Jun 2007 14:52:23 GMT

Can you please talk to your FI specialist? It looks like you are being asked to get data from the wrong place - it should come from the CO (Controlling module), see tables COBK, COEP.

BSEG is a very big table, you cannot add indexes to it because it is a Cluster table, so trying to SELECT on a non key field like KOSTL should not be done.