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?
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
Last Comment
BobJohnson66
Can you click to select DISTINCT RECORDS in the DATABASE menu?
mlmcc
mlmcc
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
ASKER
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.
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.