topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Mon, 08 Oct 2007 05:55:42 GMT

how to get data using select statement with deleting duplicate records .

Re: select statement

abdulazeez12 — Mon, 08 Oct 2007 05:58:11 GMT

use..

SELECT DISTINCT * from <DBTABLE>....

Reward if useful

Re: select statement

Former Member — Mon, 08 Oct 2007 05:58:59 GMT

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.

Re: select statement

Former Member — Mon, 08 Oct 2007 05:59:37 GMT

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

Re: select statement

Former Member — Mon, 08 Oct 2007 06:00:44 GMT

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.

Re: select statement

Former Member — Mon, 08 Oct 2007 06:04:13 GMT

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

Re: select statement

Former Member — Mon, 08 Oct 2007 06:22:42 GMT

good

go through this example,and try accordingly.

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>

reward point if helpful.

thanks

mrutyun^