topic Re: to avoid fetching duplicate records while join in select statement in Application Development and Automation Discussions

to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 10:46:41 GMT

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

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 11:05:11 GMT

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.

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 11:22:27 GMT

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

Re: to avoid fetching duplicate records while join in select statement

davis_raja — Wed, 29 Feb 2012 11:23:04 GMT

SORT the internal table and use DELETE ADJACENT DUPLICATES

Regards

Davis

Re: to avoid fetching duplicate records while join in select statement

ThomasZloch — Wed, 29 Feb 2012 11:46:10 GMT

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

Re: to avoid fetching duplicate records while join in select statement

former_member209696 — Wed, 29 Feb 2012 11:51:34 GMT

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>

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 11:57:40 GMT

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

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 12:06:00 GMT

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

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 12:09:29 GMT

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

Re: to avoid fetching duplicate records while join in select statement

former_member300258 — Wed, 29 Feb 2012 12:13:44 GMT

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

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 12:56:32 GMT

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.

Re: to avoid fetching duplicate records while join in select statement

Former Member — Wed, 29 Feb 2012 13:06:07 GMT

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.