topic Re: Inner Join help req??Urgent!!!!!!!!!!!!!!! in Application Development and Automation Discussions

Inner Join help req??Urgent!!!!!!!!!!!!!!!

Former Member — Wed, 14 Nov 2007 06:23:38 GMT

I had written the below qery : its showing error that:for pooled tble,cluster tble,projection veiws join is not allowd : 'CDPOS'.

SELECT ekko_{ebeln cdhdr}udate cdhdr_{tcode cdhdr}tabname INTO CORRESPONDING FIELDS OF TABLE it_cdhdr

FROM ( ( ekko AS ekko INNER JOIN cdhdr AS cdhdr ON ekko_{ebeln = cdhdr}objectid )

INNER JOIN cdpos AS cdpos ON ekko_{ebeln = cdpos}objectid )

WHERE ekko~ebeln = i_ebeln AND

ekko~frgke EQ 'R' AND

ekko~procstat EQ '05' AND

cdhdr~tcode IN ('ME29N' , 'ME28') AND

cdpos~tabname = 'EKKO'

GROUP BY ekko~ebeln

ekko~frgke

ekko~procstat

cdhdr~udate

cdhdr~tcode

cdpos~tabname.

so what shd i do,i need this table for the fetching data.

Plz tell me any way to solve this......its Urgent!!!!!!!!!!!!!!!!!!

Regards

Vipin

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

former_member386202 — Wed, 14 Nov 2007 06:27:26 GMT

Hi,

Joins are not allowed on cluster or pooled table, so use for all entries for that dont us ejoin.

Reagrds,

Prashant

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

gopi_narendra — Wed, 14 Nov 2007 06:27:46 GMT

You can achieve this using the FOR ALL ENTRIES

Join EKKO and CDHDR and get data into table IT_CDHDR

Now use for all entries in IT_CDHDR where OBJECTID = IT_CDHDR-OBJECTID

Regards

Gopi

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

Former Member — Wed, 14 Nov 2007 06:30:49 GMT

USE FOR ALL ENTRIES

JOINS ARE NOT USED FOR POOLED AND CLUSTER TABLES

SO ITS BETTER TO USE FOR ALL ENTRIES

IT IS GOOD WAY TO USE REGARDING PERFORMANCE ALSO FOR ALL ENTRIES BETTER THAN JINS

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

RaymondGiuseppi — Wed, 14 Nov 2007 06:40:10 GMT

Use the join between EKKO and CDHDR then use the result table (if not empty) in a SELECT FOR ALL ENTRIES on CDPOS.

CDPOS is a cluster table, data is "compressed" in an internal table (like an EXPORT TO DATABASE) so the Database system cannot create any index out of the primary index of the physical table CDCLS nor do a join.

Regards

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

Former Member — Wed, 14 Nov 2007 06:42:36 GMT

I had used it,but its showing cdpos is not in internal table,occues n specification is missing????

SELECT ekko_{ebeln cdhdr}udate cdhdr~tcode INTO CORRESPONDING FIELDS OF TABLE it_cdhdr

FROM ( ekko AS ekko INNER JOIN cdhdr AS cdhdr ON ekko_{ebeln = cdhdr}objectid )

FOR ALL ENTRIES IN cdpos

WHERE ekko~ebeln = i_ebeln AND

ekko~frgke EQ 'R' AND

ekko~procstat EQ '05' AND

cdhdr~tcode IN ('ME29N' , 'ME28') AND

cdpos-tabname = 'EKKO'

GROUP BY ekko~ebeln

ekko~frgke

ekko~procstat

cdhdr~udate

cdhdr~tcode

cdpos-tabname.

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

Former Member — Wed, 14 Nov 2007 06:45:48 GMT

We cannot join cluster or pooled tables.

do the follwoing to achieve the same functionality

select ebeln

into table tb_ebeln

from EKKO

where ebeln = i_ebeln

and frgke = 'R'

and procast = '05'.

if not tb_ebeln[] is initial.

select a~udate

a~tcode

a~tabname

into table tb_cdhdr

from cdhr as a

join cdpos as b on

a_{objectid = b}objectid

for all entries in tb_ebeln

where objectid = tb_ebeln-ebeln

and a~tcode in (ME29N,ME28)

and b~tabname = 'EKKO'.

endif.

If i_ebeln is only one value then i dont think the first query is required at all.

you can simply go ahead with the second query without for all entries and specifying the value of objectid in the where clause with the value of i_ebeln.

Regards,

Sriranjani.

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

Former Member — Wed, 14 Nov 2007 06:57:15 GMT

Hi Vipin,

U can't perform JOIN with Pooled or Cluster tables

And U can use only Internal table after FOR ALL ENTRIES <internal table>

Try to store whole data of CDPOS in the internal table and then use it..

See below links to get idea on how to work with cluster and pooled tables...

trying to join A018 and KONP .... here A018 is Pooled table... Look at below threads...

Hope it will solve your problem..

<b>Reward points if useful.</b>

Thanks & Regards

ilesh 24x7

Re: Inner Join help req??Urgent!!!!!!!!!!!!!!!

kesavadas_thekkillath — Wed, 14 Nov 2007 11:15:25 GMT

the error in ur second query is that u have used the dictionary table in for all entries.

please note that for all entries is used to join internal table values.

use the values of a itab in for all entries...u r problem wll get solved