topic Re: Join retrieving duplicate records in Application Development and Automation Discussions

Join retrieving duplicate records

Former Member — Wed, 13 Jun 2007 13:28:14 GMT

Can someone tell me why I am returning 10 records when there only should be one. I can put in a delete duplicates record after this but i would prefer to know what is wrong with the below. Tnks.

SELECT MARA_{MATNR MARA}PRDHA

MAKT_{MAKTX T179T}VTEXT

APPENDING CORRESPONDING FIELDS OF TABLE GT_WK_STORAGE

FROM MARA AS MARA

INNER JOIN MAKT AS MAKT

ON MARA_{MATNR EQ MAKT}MATNR

AND SPRAS EQ SY-LANGU

INNER JOIN T179T AS T179T

ON MARA_{PRDHA EQ T179T}PRODH

WHERE MARA~MATNR IN S_MATNR.

Re: Join retrieving duplicate records

Former Member — Wed, 13 Jun 2007 13:37:53 GMT

Hi,

I see one error in your code:

+FROM MARA AS MARA

INNER JOIN MAKT AS MAKT

ON MARA_{MATNR EQ MAKT}MATNR

AND SPRAS EQ SY-LANGU+

how can you pretend to join tables MARA and MAKT with field SPRAS, when this field does not exist in MARA ??

I think you should modify your code this way:

SELECT MATNR PRDHA

into CORRESPONDING FIELDS OF GT_WK_STORAGE

from mara

WHERE MATNR IN S_MATNR.

SELECT single MAKT_{MAKTX T179T}VTEXT

into CORRESPONDING FIELDS OF GT_WK_STORAGE

FROM makt inner join T179T

ON MAKT_{SPRAS EQ T179T}SPRAS

WHERE MAKT~MATNR = GT_WK_STORAGE-MATNR

and spras = sy-langu.

append GT_WK_STORAGE.

endselect.

Check the code, that I didn´t do it in SAP.

Re: Join retrieving duplicate records

Former Member — Wed, 13 Jun 2007 13:42:00 GMT

Tnks but the code is not incorrect.

Re: Join retrieving duplicate records

Former Member — Wed, 13 Jun 2007 13:43:27 GMT

Both tables MAKT and T179T have Language as part of their key so..

SELECT MARA_{MATNR MARA}PRDHA

MAKT_{MAKTX T179T}VTEXT

APPENDING CORRESPONDING FIELDS OF TABLE GT_WK_STORAGE

FROM MARA AS MARA

INNER JOIN MAKT AS MAKT

ON MARA_{MATNR EQ MAKT}MATNR

INNER JOIN T179T AS T179T

ON MARA_{PRDHA EQ T179T}PRODH

WHERE MARA~MATNR IN S_MATNR

<i> AND MAKT~SPRAS = SY-LANGU

AND T179T~SPRAS = SY-LANGU.</i>

One issue to be careful with is if the texts are not maintained in all sign-on languages. If they are not, you will not select anything!

Re: Join retrieving duplicate records

Former Member — Wed, 13 Jun 2007 13:49:58 GMT

Thats it . Thanks.

Yeah aware of inner join part but it forces them to make sure that they are maintained.

Points awarded and thank you for you time.

Re: Join retrieving duplicate records

Former Member — Wed, 13 Jun 2007 13:53:05 GMT

Tried to award you points but got an error message. Not sure if you got them.

Re: Join retrieving duplicate records

former_member378318 — Wed, 13 Jun 2007 13:53:43 GMT

Hi,

I don't see why this would give you duplicate enteries (it should not).

However I see that you are using the APPENDING CORRESPONDING FIELDS statement which may possibly lead to duplicates if your SELECT statement is within a LOOP.

Hope it helps