Avatar of BobJohnson66
Flag for United States of America asked on

Duplicate records in Crystal Reports 7 for Pervasive SQL (PSQL 11)

I have a custom report that I created several years ago using data from my ERP system (DBA/Evo~ERP).  Just recently, I have an anomaly that has surfaced.  I have manually stepped through the linked data files and the duplication does not exist in the data.  The odd thing is that it only seems to affect a single part number in our item master.  

I found a link to a whitepaper from Crystal Decisions titled "How to troubleshoot duplicate records from displaying on a report" that was written for Crystal Reports 9 but says it is applicable to earlier versions as well.  After reviewing, I am inclined to think that this is a data corruption problem.  Is there any way to detect such corruption and repair it?
Crystal ReportsSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon
Bill Bach

If the data was damaged in some way, then I would have expected you to find duplicate records within one or more of the individual tables.  Since you confirmed that this was NOT the case, I do not suspect data corruption at all. Instead, this looks like a SQL query issue.

Whenever you join two or more files, you can get duplication of data.  This comes in because the SQL language deals with SETS of data, not individual records.  When SQL Executes a JOIN, you are asking it to determine the intersection of one set of records to another.  If you join in the wrong direction, or if you join to a potential duplicate key instead of a primary key, or if you have a one-to-many relationship between the tables, then duplicates can (and will) occur in the resulting data set.  For a more thorough explanation, you can do a web search on "duplicate records appear in sql query results", and you'll find several decent examples which explain the issue in a lot more depth.

To truly understand what is happening, we'd need to see the query, the query results, the relationships between the tables, the source data set for a duplicate row.
Mike McCracken

Can you click to select DISTINCT RECORDS in the DATABASE menu?


The big question is why now?  And why just this one item?  This report has been running in its current state for literally years before this popped up.   I found an old whitepaper [https://archive.sap.com/documents/docs/DOC-21403] from SAP titled " How to troubleshoot duplicate records from displaying on a report".  I will try to work on this over the weekend.  My plan is to create a formula field that combines the Sales Order Number and SO Line Number since that is what makes the detail unique.   Next I will create a subgroup on that formula field.  Then I should be able to check the box to suppress duplicates.  I hope.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Bill Bach

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Technically, this is still unresolved.  However, I spoke to our ERP provider and determined that this is a one off situation at this time that will clear through by the end of the month.  If this type of situation recurs, I will dig into it further.