to avoid fetching duplicate records while join in select statement

[Former Member]

Hi experts,

SELECT a~adatsoll
         a~adattats
         a~gernr
         a~equnr
         b~anlage
         b~ableinh
         b~abrvorg
         b~trigstat
         INTO TABLE lt_meter
         FROM eabl AS a
         INNER JOIN etrg AS b
         ON a~adatsoll  = b~adatsoll
         WHERE a~adattats IN s_date
         AND  a~adattats NE a~adatsoll .

As result of this select query, I am getting duplicate records.

Please provide me solution to avoid fetching duplicate records.

Thanks in advance,

Kiruthika P

[Former Member]

You can use DISTINCT after SELECT.

But I advice you to sort the table by the fields you want and use a delete adjacent duplicates statement.

[Former Member]

Hi,

You can use DELETE ADJACENT DUPLICATES FROM itab COMPARING ALL FIELDS statemnet after the SELECT query, which deletes all the duplicate entries in the internal table. If you want to delete based on any fields, you can mention that in place of ALL FIELDS.

<removed by moderator>

Thanks,

Harish

Edited by: Thomas Zloch on Feb 29, 2012

[davis_raja]

SORT the internal table and use DELETE ADJACENT DUPLICATES

Regards

Davis

[ThomasZloch]

I'm not so much into the IS-U database structure, but a join condition using only a date field cannot be correct, this leads to the duplicates in this case. You should go back into design mode and find out proper join conditions for these tables (involving key or foreign key fields), if there are any. Most importantly, read online documentation to understand how joins work and how to properly construct them.

Thomas

[former_member209696]

After fetching the data Sort the internal table ..

 
SORT <itab> ASCENDING/DESCENDING BY <Field>

Then use delete adjacent duplicate.

DELETE ADJACENT DUPLICATE FROM <itab> COMPARING <All fields/ Specify fields>

[Former Member]

Hi all,

My requirement is to avoid duplicates from select query itself.

Not after result of select query. i.e. not to use statement delete adjacent duplicates.

Suggest if there any other option instead this.

Thanks,

Kiruthika P

[Former Member]

Hi,

If you are not fetching huge amount of data, Use:

FOR ALL ENTRIES instead of Inner Join, this might solve your issue.

Example:

IF NOT itab[] IS INITIAL.
  SELECT xxxx
    FROM dbtable
      FOR ALL ENTRIES IN itab
        WHERE xxx
ENDIF.

Thanks,

Harish

[Former Member]

As someone already suggested: Use the keyword 'DISTINCT' after SELECT. This will only keep 1 occurence in the result set for duplicate entries in the database table.

Regards,

Philippe

[former_member300258]

HI,

if you want to use JOINS, you must specify the proper KEY fields of the table in the join conditions . if not, Better don't use JOIN, instead use FOR ALL ENTRIES.

SELECT adatsoll adattats gernr equnr FROM eabl INTO TABLE it_eabl 
              WHERE adattats IN s_date.

IF it_eabl[] IS NOT INITIAL.
   SELECT anlage ableinh abrvorg trigstat FROM etrg  INTO TABLE it_etrg
                 FOR ALL ENTRIES IN it_eabl
                 WHERE adatsoll = it_eabl-adatsoll.       " you may also any other conditions as per ur req.
ENDIF.

GOOD LUCK

[Former Member]

Hi,

To avoid duplicate entries you should

1) sort internal table

2) Delete adjecent duplicates

3) go for for select....for all entries

then you will not get duplicate records.

Regards,

Sateesh.

[Former Member]

Hi,

If you see the primary keys of those two tables, and the join fields in your sql, it is very much different.

ABLBELNR

ANLAGE
ABRDATS
ABRVORG

The join is not at all using any of the primary keys of those 2 tables. hence duplicate records can come.

regards,

amit m.