Duplicate records

[Former Member]

Hi

I have written one query like follow, but some records are duplicating please let me know what is problem

SELECT catsdb~pernr

pa0001~ename

t001p~btext

t528t~plstx

• t542t~atx

catsdb~raufnr

aufk~kdauf

afih~qmnum

vbak~vkbur

t003p~txt

aufk~ernam

aufk~auart

catsdb~catshours

t554t~atext

catsdb~workdate

catsdb~status

catsdb~lstar

• catsdb~lstnr

catsdb~raufnr

catsdb~werks

catsdb~autyp

catsdb~awart

catsdb~lgart

t503t~ptext

catsdb~skostl

catsdb~rkostl

aufk~objnr

catsdb~laeda

catsdb~laetm

catsdb~aenam

catsdb~apdat

catsdb~apnam

crtx~ktext_up

t528t~plans

t503t~persk

INTO CORRESPONDING FIELDS OF TABLE i_output

FROM catsdb

INNER JOIN pa0001 ON catsdb_{pernr EQ pa0001}pernr AND

pa0001~endda GT sy-datum

INNER JOIN aufk ON catsdb_{raufnr EQ aufk}aufnr

INNER JOIN afih ON afih_{aufnr EQ aufk}aufnr

LEFT JOIN crtx ON crtx_{objid EQ afih}gewrk AND

crtx_{objty EQ afih}pm_objty AND

crtx~spras EQ sy-langu

LEFT JOIN vbak ON vbak_{vbeln EQ aufk}kdauf

LEFT JOIN t001p ON t001p_{werks EQ pa0001}werks AND

t001p_{btrtl EQ pa0001}btrtl

LEFT JOIN t528t ON t528t_{plans EQ pa0001}plans AND

t528t~sprsl EQ sy-langu AND

t528t~otype EQ 'S'

LEFT JOIN t554t ON t554t_{awart EQ catsdb}lgart AND

t554t~sprsl EQ sy-langu

• LEFT JOIN t542t ON t542t_{ansvh EQ pa0001}ansvh AND

• t542t~spras EQ 'EN'

LEFT JOIN t003p ON t003p_{auart EQ aufk}auart AND

t003p~spras EQ sy-langu

LEFT JOIN t503t ON t503t_{persk EQ pa0001}persk AND

t503t~sprsl EQ sy-langu

WHERE catsdb~workdate IN s_workdt AND

pa0001~werks EQ p_werks

ORDER BY catsdb_{pernr catsdb}raufnr.

Regards

Sebastian John

[ThomasZloch · Accepted solution]

At quick glance the cause could be your join on PA0001. You link it by PERNR and ENDDA >= SY-DATUM, but it has more primary key fields. There might be cases of several rows with ENDDA >= SY-DATUM for one PERNR.

Thomas

[Former Member · Accepted solution]

hi,

please first sort internal table after that use statement

DELETE ADJACENT DUPLICATES FROM <INTERNAL TABLE>.

[Former Member]

Thanks

[Former Member]

Hi Sebastian,

you can use joins, they would perform better than for all entries... to prove see the link provided,

[;

now since you are using inner joins with so many tables what exactly happens is it takes entries from all the tables and the cardinality is many to many. for each record of left table it takes all the corresponding entries from the right table.

so even though you use all the key fields for the left table if there are more than one entry in the right table it will give you more than one entries duplicating the key fields in the resultant table.

this is the reason you are getting duplicate records.

The best way could be

after the select query

sort the internal table

and then use DELETE ADJACENT DUPLICATES FROM itab

[COMPARING {comp1 comp2 ...}|{ALL FIELDS}]... .

Regards,

Siddarth

[Former Member]

Hi,

First of all i request you not to use joins inthe select statemments .

Because it will reduce ur performance.

So use like

select filds list from table1 into itab where condition.

select fild list fromtable2 into itab2 for all entries in itab1 where condition.

and so on.

Then you will get corect out put.

Regards

[Former Member]

Hi Sebastian,

Trying these two things should help.

In case the records collected in the internal table are incorrect, check to see if the join leaves out any primary key from the tables used that can be used.

In case the problem is just of duplicate records and all desired records are getting collected-

Sort the internal table after the select statement (SORT i_output)

Remove the duplicate entries (DELETE DUPLICATES FROM i_output)

In case this does not help, could you please give some examples of the duplicate record getting extracted?

Regards,

Nimish

[Former Member]

Its CATSDB table so i belive there can be many dupliacte records as its for capturing

time realted data

so now whats the problem.

use delete duplicates for your internal table?

[Former Member]

Hi,

After populating records in internal table ,

please sort the interanl table with they keyfields

then u can use delete adjacent duplicates based upon the field which is duplicating.

so that dupklicated record will be deleted from the internal tbale .

hope this will help u..

[faisal_altaf2]

Hi, John

Please Check for all key filed in join you must take care of all key filed in join other wise you will get wrong result multiple records.

Please first check you select not use DELETE ADJACENT DUPLICATES because if duplicates are because of wrong select than you will not be able to get your desire result.

Regards,

Faisal

[Former Member]

Hi,

The records that are being duplicated are because of Left join being used in the query.

Left join works like getting all records of the table on the left and matching ones from the right. This must be causing duplication. Try using inner join instead Left joins.

or

delete adjacent duplicates from internal table

Regards,

Ibrar

[Former Member]

You get duplicate records becuase you are using INNER Joins in your Select Query.

Sort your internal table and use DELETE ADJACENT DUPLICATES on the internal table afterwards.

[Former Member]

Inner join I have done is ok, that is with primary the key in the table, it should be duplicate.

Regards

Sebastian John

[Former Member]

inner join with primary key, my asumption it will not duplicate

please let me know.

Regards

Sebastian John

[Former Member]

This message was moderated.