topic Re: delete duplicates in Application Development and Automation Discussions

delete duplicates

Former Member — Wed, 18 Jan 2017 12:39:04 GMT

I need to delete duplicates, my code is

SELECT k~ebeln
 k~lifnr
 k~bedat k~bsart
 p~werks p~lgort p~adrnr
 a~name1 a~name2 a~city1 a~post_code1
 a~street a~house_num1 a~country
 FROM ekko AS k
 JOIN ekpo AS p
 ON k~ebeln EQ p~ebeln
 JOIN adrc AS a
 ON p~adrnr EQ a~addrnumber
 INTO CORRESPONDING FIELDS OF TABLE t_tab
 WHERE k~bsart IN s_bsart
 AND k~lifnr IN s_lifnr
 AND k~bedat IN s_bedat.

DELETE ADJACENT DUPLICATES FROM t_tab 
 COMPARING name1 street house_num1 post_code1 city1 country.

I want to have all duplicates deleted if the comparing fields are same but EBELN should be ignored, I only need to show 1 EBELN , 1 WERKS and 1 LIFNR. LGORT is normally empty.

like this example, only line 1 should be showed:

ebeln lifnr werks lgort name1 name2 street no city country
123 555 001 space test1 test2 street1 XX DE
124 555 001 space test1 test2 street1 XX DE
125 555 001 space test1 test2 street1 XX DE

The "delete adjacent duplicates"-statement deletes records where also ebeln has duplicates. Is there a possibility to ignore ebeln or may I select one ebeln with those compared fields for 1 row. They only want to see our plants adresses and the vendor but the ebeln only once as "reference value"

Re: delete duplicates

ChrisSolomon — Wed, 18 Jan 2017 12:43:19 GMT

You need to first sort the result (t_tab) into the order you want (so your "duplicates" line up) and then call the "delete adjacent" on them.

Re: delete duplicates

Former Member — Wed, 18 Jan 2017 12:48:35 GMT

so I have

SORT t_tab BY lifnr werks lgort ASCENDING.

this might be the fault 😮

Re: delete duplicates

Former Member — Wed, 18 Jan 2017 14:19:04 GMT

thank you! that was a bad mistake I did

Re: delete duplicates

ChrisSolomon — Wed, 18 Jan 2017 14:45:43 GMT

Not "bad"....you just missed it....happens to all of us....comes with being a developer....can I tell you how many times I misspelled a keyword or left off a period or comma somewhere? Oh no....I NEVER do that. haha