We help IT Professionals succeed at work.

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?
Comment
Watch Question

Bill BachPresident and Btrieve Guru

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Author

Commented:
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.
President and Btrieve Guru
Commented:
Answering those questions requires the aforementioned data, of course.

Author

Commented:
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.