topic Re: select statement n joining condtion.. in Application Development and Automation Discussions

select statement n joining condtion..

Former Member — Wed, 11 Jan 2006 22:14:32 GMT

Hi Friends,

In a Report, I have the Material Numbers dispayed as of now. I have to select/display the rate(condition amount) for each Material Number that is on the list....

I have realized that I can relate the Mat. No with the Rate by using 3 tables....

1.MARA

2.A505

3.KONP.

I'm thinking to relate this rate(KONP-KBETR) with Mat No(MARA-MATNR) like this....

JOIN the MARA-MATNR with A505-MATNR and then A505-KNUMH with KONP-KNUMH and then retrieve KONP-KBETR...

but i dont know how to write the select and joining condition...

as of now... i have a INTERNAL TABLE :- i_matdata.

which contains the mat. no's

help me, plz...

thanks,

raj

Re: select statement n joining condtion..

Former Member — Wed, 11 Jan 2006 22:35:49 GMT

THIS IS ONE SOLUTION

SELECT A~MATNR

B~KNUMH

INTO CORRESPONDING FIELDS OF TABLE I_TAB1

FROM MARA AS A INNER JOIN

A505 AS B ON

A_{MATNR = B}MATNR

FOR ALL ENTRIES IN I_MATNR

WHERE A~MATNR = I_MATNR-MATNR.

SORT I_TAB1 BY KNUMH.

SELECT A~KNUMH

B~KBETR

INTO CORRESPONDING FIELDS OF TABLE I_TAB2

FROM A505 AS A INNER JOIN

KONP AS B ON

A_{KNUMH = B}KNUMH

FOR ALL ENTRIES IN I_TAB1

WHERE A~KNUMH = I_TAB1-KNUMH.

LOOP AT I_TAB1.

READ TABLE I_TAB2 WITH KEY KNUMH = I_TAB1-KNUMH.

I_TAB3-MARNR = I_TAB1-MATNR.

I_TAB3-KBETR = I_TAB2-KBETR.

APPEND I_TAB3.

ENDLOOP.

Re: select statement n joining condtion..

former_member186741 — Wed, 11 Jan 2006 23:04:06 GMT

My site doesn't have A505 with matnr and knumh but I found a similar one A550 so you could do something like this:

select mara_{matnr konp}kbetr

from mara

join a550 on mara_{matnr = a550}matnr

join konp on konp_{knumh = a550}knumh

into corresponding fields of table i_rates

FOR ALL ENTRIES IN i_matdata

WHERE mara~MATNR = i_matdata-MATNR