10-08-2007 6:55 AM
how to get data using select statement with deleting duplicate records .
10-08-2007 6:58 AM
10-08-2007 6:58 AM
Hi,
You could try using the DISTINCT keyword or provide the key fields in the selection criteria.
e.g. SELECT DISTINCT <field> FROM <table>.
but this would not the the best approach, rather you select all then sort by a field and use
DELETE ADJACENT DUPLICATES FROM <table>.
Regards,
Samson Rodrigues.
10-08-2007 6:59 AM
Remember few things while using SELECT FOR ALL ENTRIES
1) Check the Driver table[] NOT INITIAL becaue if it is null it will retrieve all the records form the database.
2) SORT ITAB[] by the key word this is because you need to delete the adjacent duplicates next.
3)DELETE ADJACENT DUPLICATES COMPARING <FIELD> this is needed because the Target table is filled with Distinct records form the database.
Please give me reward point If it is useful
Thanks
Murali Poli
10-08-2007 7:00 AM
Hi,
To avoid duplicates,u can either use SELECT DISTINCT statements or After selecting the entries, SORT the table and DELETE ADJACENT DUPLICATES.
select distinct matnr into table dis_matnr from makt.
describe table dis_matnr lines ln.
Or try this.
select count (*) from makt into ln group by matnr having
matnr in ( select distinct matnr from makt ).
Regards,
Priyanka.
10-08-2007 7:04 AM
Hi Chaaya,
Go through the example mentioned below to get a clear idea.
Check the below program :
data : begin of itab occurs 0,
num(3) type n,
fld(4) type c,
end of itab.
start-of-selection.
itab-num = '100'.
itab-fld = 'aa'.
append itab.
itab-num = '100'.
itab-fld = 'bb'.
append itab.
itab-num = '200'.
itab-fld = 'cc'.
append itab.
itab-num = '200'.
itab-fld = 'dd'.
append itab.
sort itab by num fld.
DELETE ADJACENT DUPLICATES FROM itab comparing num.
loop at itab.
write:/ itab-num,itab-fld.
endloop.
Reward if helpful.
Regards,
Harini.S
10-08-2007 7:22 AM
hi
good
go through this example,and try accordingly.
SELECT DISTINCT AFKOAUFNR AFKOPLNBEZ AFKOGAMNG AFKOGLTRP MARABISMT VBAKAUDAT VBAP~KWMENG
VBEP~MBDAT
<b>INTO CORRESPONDING FIELDS OF table ITAB</b>
FROM AFKO
INNER JOIN MARA ON MARAMATNR = AFKOPLNBEZ
INNER JOIN VBAP ON VBAPMATNR = MARAMATNR
INNER JOIN VBAK ON VBAKVBELN = VBAPVBELN
INNER JOIN VBEP ON VBEPVBELN = VBAPVBELN
INNER JOIN VBUK ON VBUKVBELN = VBAPVBELN
WHERE AFKOAUFNR IN S_AUFNR and tmp_date = VBAKAUDAT.
WHERE AFKOAUFNR IN S_AUFNR and AFKOGLTRP = VBAK~AUDAT
AND VBAKAUDAT >= AFKOAUFNR
AND VBUK~GBSTK = 'B'.
<b>* ENDSELECT</b>
<b>delete adjactent duplicates from itab comparing aufnr.</b>
reward point if helpful.
thanks
mrutyun^