getting duplicate from a select statement.

[Former Member]

SELECT DISTINCT AFKO_{AUFNR AFKO}PLNBEZ AFKO_{GAMNG AFKO}GLTRP

MARA_{BISMT VBAK}AUDAT VBAP~KWMENG

VBEP~MBDAT

INTO CORRESPONDING FIELDS OF ITAB

FROM AFKO

INNER JOIN MARA ON MARA_{MATNR = AFKO}PLNBEZ

INNER JOIN VBAP ON VBAP_{MATNR = MARA}MATNR

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN VBEP ON VBEP_{VBELN = VBAP}VBELN

INNER JOIN VBUK ON VBUK_{VBELN = VBAP}VBELN

• WHERE AFKO_{AUFNR IN S_AUFNR and tmp_date = VBAK}AUDAT.

WHERE AFKO_{AUFNR IN S_AUFNR and AFKO}GLTRP = VBAK~AUDAT

AND VBAK_{AUDAT >= AFKO}AUFNR

AND VBUK~GBSTK = 'B'.

getting duplicate records how can i distinct it

[Former Member]

you can use the delete adjacent duplicate records after the select statement.

Regards

Anurag

[Former Member]

Hi,

use

<b>delete adjacent duplicates</b>

statement after the select statement..

Regards,

Santosh

[Former Member]

can u show the example for the delete statement.

[Former Member]

SELECT...

ENDSELECT.

DELETE ADJACENT DUPLICATES FROM itab.

-Help-

DELETE ADJACENT DUPLICATES FROM itab.

Extras:

1. ... COMPARING f1 f2 ...

2. ... COMPARING ALL FIELDS

Effect

Deletes adjacent duplicate entries from the internal table itab. If there are n duplicate entries in succession, the first entry is retained, and the following n-1 entries are deleted.

Two lines are regarded as duplicates if their keys are identical.

The Return Code is set as follows:

SY-SUBRC = 0:

At least one duplicate was found, and at least one entry was deleted.

SY-SUBRC = 4:

No duplicates found, no entries deleted.

Addition 1

... COMPARING f1 f2 ...

Effect

Two lines of the internal table itab are regarded as duplicates if they have identical contents in the fields f1, f2, ...

Addition 2

... COMPARING ALL FIELDS

Effect

Two lines of the internal table are regarded as duplicates if all of their field contents are identical.

Notes

The DELETE ADJACENT DUPLICATES statement works particularly well if you have sorted the internal table itab according to the fields that you want to compare when looking for duplicates. In this case, deleting adjacent duplicates is the same as deleting all duplicates. The direction of the sort is irrelevant.

If you do not know a comparison expression until runtime, you can specify it dynamically as the contents of the field name in the expression COMPARING ... (name) .... If name is empty at runtime, the comparison expression is ignored. If name contains an invalid component name, a runtime error occurs.

You can further restrict comparison expressions - both static and dynamic - by specifying offset and length.

Note

Performance:

When you delete a line of an internal table, index maintenance costs are incurred. These depend on the index of the table. The runtime is independent of the width of the table line.

Deleting a line from the middle of an internal table with 200 entries usually requires around 10 msn (standardized microseconds).

When you delete a set of entries using "DELETE itab FROM idx1 TO idx2." or "DELETE itab WHERE ...", index maintenance costs are only incurred once. This means that it is considerably more efficient than deleting entries in a LOOP.

If you want to delete adjacent duplicate entries from an internal table, use the variant " DELETE ADJACENT DUPLICATES FROM itab." instead of a LOOP construction.

---Please assign points for helpful answers !! ---

Message was edited by: Anurag Bankley

Message was edited by: Anurag Bankley

[former_member195698]

By "getting duplicate records" what do you mean ?

The query u have written will give only distinct records based on the fields that u have selected.

To get distinct records based on certain fields, select only those fields in the select query.

If that can't be done then sort the internal table and then delete adjacent duplicates comparing the required fields.

[Former Member]

SELECT DISTINCT AFKO_{AUFNR AFKO}PLNBEZ AFKO_{GAMNG AFKO}GLTRP

MARA_{BISMT VBAK}AUDAT VBAP~KWMENG

VBEP~MBDAT

INTO CORRESPONDING FIELDS OF ITAB

FROM AFKO

INNER JOIN MARA ON MARA_{MATNR = AFKO}PLNBEZ

INNER JOIN VBAP ON VBAP_{MATNR = MARA}MATNR

INNER JOIN VBAK ON VBAK_{VBELN = VBAP}VBELN

INNER JOIN VBEP ON VBEP_{VBELN = VBAP}VBELN

INNER JOIN VBUK ON VBUK_{VBELN = VBAP}VBELN

• WHERE AFKO_{AUFNR IN S_AUFNR and tmp_date = VBAK}AUDAT.

WHERE AFKO_{AUFNR IN S_AUFNR and AFKO}GLTRP = VBAK~AUDAT

AND VBAK_{AUDAT >= AFKO}AUFNR

AND VBUK~GBSTK = 'B'.

APPEND ITAB.

ENDSELECT.

<b>where do I need to put the delete adjacent duplicates.</b>

[Former Member]

SELECT DISTINCT AFKO~AUFNR AFKO~PLNBEZ AFKO~GAMNG AFKO~GLTRP
MARA~BISMT VBAK~AUDAT VBAP~KWMENG
VBEP~MBDAT
INTO CORRESPONDING FIELDS OF ITAB
FROM AFKO
INNER JOIN MARA ON MARA~MATNR = AFKO~PLNBEZ
INNER JOIN VBAP ON VBAP~MATNR = MARA~MATNR
INNER JOIN VBAK ON VBAK~VBELN = VBAP~VBELN
INNER JOIN VBEP ON VBEP~VBELN = VBAP~VBELN
INNER JOIN VBUK ON VBUK~VBELN = VBAP~VBELN
* WHERE AFKO~AUFNR IN S_AUFNR and tmp_date = VBAK~AUDAT.
WHERE AFKO~AUFNR IN S_AUFNR and AFKO~GLTRP = VBAK~AUDAT
AND VBAK~AUDAT >= AFKO~AUFNR
AND VBUK~GBSTK = 'B'.
if sy-subrc eq 0.
<b>DELETE ADJACENT DUPLICATES FROM itab.</b> 
endif.

[RichHeilman]

You might want to do a table fetch instead of a SELECT...ENDSELECT. It may improve performance.

SELECT DISTINCT AFKO~AUFNR AFKO~PLNBEZ AFKO~GAMNG AFKO~GLTRP MARA~BISMT VBAK~AUDAT VBAP~KWMENG
VBEP~MBDAT
<b>INTO CORRESPONDING FIELDS OF table ITAB</b>
FROM AFKO
INNER JOIN MARA ON MARA~MATNR = AFKO~PLNBEZ
INNER JOIN VBAP ON VBAP~MATNR = MARA~MATNR
INNER JOIN VBAK ON VBAK~VBELN = VBAP~VBELN
INNER JOIN VBEP ON VBEP~VBELN = VBAP~VBELN
INNER JOIN VBUK ON VBUK~VBELN = VBAP~VBELN
* WHERE AFKO~AUFNR IN S_AUFNR and tmp_date = VBAK~AUDAT.
WHERE AFKO~AUFNR IN S_AUFNR and AFKO~GLTRP = VBAK~AUDAT
AND VBAK~AUDAT >= AFKO~AUFNR
AND VBUK~GBSTK = 'B'.
<b>* ENDSELECT</b>
<b>delete adjactent duplicates from itab comparing aufnr.</b>

Regards,

Rich Heilman

[Former Member]

Hi Kamlesh,

DELETE ADJACENT DUPLICATES FROM ITAB. Use comparing ..say

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING AUFNR if you want the distinction based on a particular field.

Regards,

Vidya.

[Former Member]

Hi

If I understand correctly, you mean that the Select statement is returning duplicate rows in spite of the fact you have used the DISTINCT keyword. This suggests that you do not have true duplicates - the distinct keyword will compare ALL of the fields in you SELECT clause and will only exclude those that are identical.

You would therefore be better off to not use the Distinct keyword (SAP can then optimise the Select so it should perform better) and then immediately afterwards sort the itab by the fields you want the data to be unique on and then use the delete duplicates statement:

  SORT itab BY aufnr plnbez gamng.
  DELETE ADJACENT DUPLICATES FROM i_grant COMPARING aufnr plnbez gamng.

Also, you might want to review the whole SQL statement - for example, do you really want to find records where the Sales Document date (vbak-audat) is greater than the Order Number of AFKO (afko-aufnr)??? It is also likely to be quite slow as you are joining a lot of tables - with VBAK and VBAP being particularly large ones. Think about using some of the index tables like VAPMA or breaking the statement down into smaller chucnks and using the 'FOR ALL ENTRIES' statement.

Hope this helps

Andy