Skip to Content
avatar image
Former Member

select statement

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • avatar image
    Former Member
    Oct 08, 2007 at 05:58 AM

    use..

    SELECT DISTINCT * from <DBTABLE>....

    Reward if useful

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 08, 2007 at 05: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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 08, 2007 at 05: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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 08, 2007 at 06: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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 08, 2007 at 06: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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 08, 2007 at 06: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^

    Add comment
    10|10000 characters needed characters exceeded