topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Wed, 29 Aug 2007 11:27:27 GMT

Hello,

I've got the following problem in BI 7 :

I've got two tables : CRM_JCDS and TJ30.

In the first one, there are records like :

objnr----

stat

469C4D7D25DD5411E1000000AC1E9ACA E0007

469C4D7D25DD5411E1000000AC1E9ACA I1002

In the second one :

stsma----

estat

ZSTOPAF1 E0001

ZSTOPAF1 E0002

ZSTOPAF1 E0007

I want to join these two tables in order to put in result the status which is the same in the two tables... (the result is : status(5) type c)

But I'm not very good in ABAP.

Someone could give to me the code ?

Cheers,

Vince

Re: Inner Join

Former Member — Wed, 29 Aug 2007 11:31:38 GMT

Hi,

data:begin of itab occurs 0,"this is an internal table to *hold the datawhich satisfies ur condition

objnr type CRM_JCDS-objnr,

stat type CRM_JCDS-stat,

stsma type TJ30-stsma,

end of itab.

select a_{objnr a}stat b_{stsma into corresponding fields of itab from CRM_JCDS as a inner join TJ30 as b on a}stat = b~estat."join condition

write:/ itab-objnr,itab-stat,itab-stsma.

endselect.

<b>reward if helpful</b>

rgds,

bharat.

Re: Inner Join

Former Member — Wed, 29 Aug 2007 12:00:22 GMT

Hi Bharat,

In fact the table CRM_JCDS has more records like

objnr----

stat

469C4D7D25DD5411E1000000AC1E9ACA E0007

469C4D7D25DD5411E1000000AC1E9ACA I1002

477777777777777771000000AC1E9ACA E0008

477777777777777771000000AC1E9ACA I1002

477766666666677771000000AC1E9ACA E0007

477766666666677771000000AC1E9ACA I1002

We know still the objnr.

So we must select the good status from CRM_JCDS for the inner join.

Can you do that ?

Re: Inner Join

Former Member — Wed, 29 Aug 2007 12:08:27 GMT

Hi,

Try the code below:

data:begin of itab occurs 0,

objnr type CRM_JCDS-objnr,

stat type CRM_JCDS-stat,

stsma type TJ30-stsma,

end of itab.

select a_{objnr a}stat b~stsma

into corresponding fields of table itab

from CRM_JCDS as a inner join TJ30 as b

on a_{stat = b}estat

where a~stat+(1) = 'E'.

Loop at itab.

write:/ itab-objnr,itab-stat,itab-stsma.

endloop.

Regards,

Amit

Re: Inner Join

Former Member — Wed, 29 Aug 2007 12:09:35 GMT

Hi,

in this condition also the above statement will work

can u explain in more clear way

<b>reward if helpful</b>

rgds,

bharat.

Re: Inner Join

Former Member — Wed, 29 Aug 2007 12:20:02 GMT

I would put a condition, but i'm not good in ABAP !!

The good code (for me) is :

SELECT a~stat

INTO statut

FROM crm_jcds AS a

INNER JOIN tj30 AS b

ON a_{stat = b}estat

WHERE a~objnr = guid_ds.

ENDSELECT.

Thanks for your help.

Points are assigned.

Cheers,

Vince.