on 05-20-2009 7:55 AM
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 catsdbpernr EQ pa0001pernr AND
pa0001~endda GT sy-datum
INNER JOIN aufk ON catsdbraufnr EQ aufkaufnr
INNER JOIN afih ON afihaufnr EQ aufkaufnr
LEFT JOIN crtx ON crtxobjid EQ afihgewrk AND
crtxobjty EQ afihpm_objty AND
crtx~spras EQ sy-langu
LEFT JOIN vbak ON vbakvbeln EQ aufkkdauf
LEFT JOIN t001p ON t001pwerks EQ pa0001werks AND
t001pbtrtl EQ pa0001btrtl
LEFT JOIN t528t ON t528tplans EQ pa0001plans AND
t528t~sprsl EQ sy-langu AND
t528t~otype EQ 'S'
LEFT JOIN t554t ON t554tawart EQ catsdblgart AND
t554t~sprsl EQ sy-langu
LEFT JOIN t542t ON t542tansvh EQ pa0001ansvh AND
t542t~spras EQ 'EN'
LEFT JOIN t003p ON t003pauart EQ aufkauart AND
t003p~spras EQ sy-langu
LEFT JOIN t503t ON t503tpersk EQ pa0001persk AND
t503t~sprsl EQ sy-langu
WHERE catsdb~workdate IN s_workdt AND
pa0001~werks EQ p_werks
ORDER BY catsdbpernr catsdbraufnr.
Regards
Sebastian John
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.