Duplicate records in SAP query
Go to solution
Former Member
2013 Jul 22 12:00 PM
10,842
SAP Managed Tags
ABAP Development
Hi experts,
I've created a report showing statistics on customer invoice clearing.
I've used table BKPF with left outer joins to BSID and BSAD.
We have several Real Estate invoices that have duplicate document
numbers. In some cases there are entries in BSID and BSAD with the
same documents number. This is creating duplicate records in my
report. Eg if a certain document number appears 5 times in BSID and
10 times in BSAD, I'm getting 50 records for that doc number in my
report.
Does anyone know a way of manipulating the infoset to ensure i don't
get duplicate records?
On a seperate note relating to some different queries I've created, is
there a way of including only the first instance of a record? EG a
document number that may have several records, but there's no way
of determining the first instance of it just by using filtering.
Many thanks
Dave
1 ACCEPTED SOLUTION
Former Member
2013 Jul 26 10:20 AM
4,519
SAP Managed Tags
ABAP Development
Hi Dave,
would generating a program from the query and
replacing SELECT with
SELECT DISTINCT ...
work for you? Since it - by definition - eliminates duplicates from a
selection.
If you need to retrieve only a given number of records you should
use SELECT ... UP TO <n> ROWS:
Example to select the first MARA entry:
DATA:
lt_mara TYPE TABLE OF mara.
SELECT *
FROM mara
INTO TABLE lt_mara
UP TO 1 ROWS.
Regards,
Chris
12 REPLIES
Former Member
2013 Jul 22 1:13 PM
4,519
if you use inner join instead of left outer join the duplicates will be
removed.
Former Member
In response to Former Member
2013 Jul 22 2:11 PM
4,519
Thanks for the reply Sai, i need the joins to be left outer for the report
to work. Otherwise only documents that appear both in BSID and
BSAD will be returned.
jogeswararao_kavala
Active Contributor
In response to Former Member
2013 Jul 23 7:54 AM
4,519
The solution to your problem will be:
1. Having another join between tables which would eliminate this
duplication.
OR
2. In Extras tab Code tab --. Record Processing section you give your
condition such as
Check table-field <> 'xyz123'.
You have to examine what is this condition. which one of these
duplicate records you need and which one you do not need. Take a
field value from the undesired lines (which has got different value in
desired lines) and give form this check syntax.
In other words, you need to identify the lines you do not need by one
or more field values and form the Check syntax.
Hope you've understood.
Jogeswara Rao K
Message was edited by: Jogeswara Rao Kavala
Former Member
In response to Former Member
2013 Jul 23 11:00 AM
4,519
Hi Jogeswara,
The other field which differentiates the entries is BUZEI. Unfortunately
this field isn't in BKPF. I've tried using FAGLFLEXA instead of BKPF (and
joined BUZEI) which removes the above duplicates, but then gives me
duplicates for other documents. Other than that the only difference
between each line on a Real Estate invoice is the text.
Is there any way of adding code so that only the 15 entries are pulled
through rather than 50? The undesired entries are exactly the same
so I don't see how i can add conditions to remove them.
Many thanks
Dave
jogeswararao_kavala
Active Contributor
In response to Former Member
2013 Jul 23 3:53 PM
4,519
Check syntax is the only option in SAP query to control duplicates.
Out of 10 such cases I can say I could overcome duplicates with this
tool in 8 cases, but the other 2 forced me to switch to an ABAP
program.
'Delete adjacent duplicates' does not work here, because accessing
internal tables is another challenge in SAP query. For me it is almost
impossible. Such are the limitations of SAP query, where ABAP coding
becomes essential.
But, I still feel that you could further investigate and perform the
above Check syntax trials in combination with more joins between
tables, to achieve your result. Always my senses say in such cases,
there is some way, but to find the same is very difficult, because we
have to scan in-numerous fields of the tables involved, to find out
which can differentiate the duplicates.
Also, you can try by rearranging the left outer join links between
tables.
Jogeswara Rao K
RaymondGiuseppi
Active Contributor
2013 Jul 23 7:44 AM
4,519
Would it not be much easier to perform two selects : one on BSID
(INTO TABLE) and one on BSAD (APPENDING TABLE) ?
Regards,
Raymond
Former Member
In response to RaymondGiuseppi
2013 Jul 23 11:01 AM
4,519
Hi Raymond,
Please can you elaborate on that a little, can it be done in SAP Query?
Many thanks
Dave
RaymondGiuseppi
Active Contributor
In response to RaymondGiuseppi
2013 Jul 31 11:04 AM
4,519
You could use logical database DDF to define your SQ02 InfoSet.
Regards,
Raymon
Former Member
2013 Jul 26 10:20 AM
4,520
Hi Dave,
would generating a program from the query and
replacing SELECT with
SELECT DISTINCT ...
work for you? Since it - by definition - eliminates duplicates from a
selection.
If you need to retrieve only a given number of records you should
use SELECT ... UP TO <n> ROWS:
Example to select the first MARA entry:
DATA:
lt_mara TYPE TABLE OF mara.
SELECT *
FROM mara
INTO TABLE lt_mara
UP TO 1 ROWS.
Regards,
Chris
gaurab_banerji
Active Participant
2013 Jul 26 12:54 PM
4,519
since you are not using inner join there will be duplicate records..
sort based upon preference of fields and delete adjacent duplicates..
you may also use select distinct as christian has suggested
Former Member
In response to gaurab_banerji
2013 Jul 26 3:20 PM
4,519
Thanks Gaurab. Can this be done in SAP query?
gaurab_banerji
Active Participant
In response to gaurab_banerji
2013 Jul 31 10:58 AM
4,519
you can write program lines in sap queries in sq02
