Caution when using “Select Distinct”

Monday 19 June 2017 @ 7:56 pm

There is one thing you have to watch out for when you use the “Select Distinct Records” setting to eliminate duplicate records. There is often a difference between what you consider a duplicate and what SQL considers a duplicate.

When you activate this feature (Database > Select Distinct Records) Crystal changes the first line of the SQL query from:

Select

to

Select Distinct

This causes the SQL engine to look for duplicates within your raw results.  To the SQL engine, duplicates are any records that have the exact same value for every column in the results or every field listed in the SELECT clause of the query. This will be every field used by the report in any way.  You can see which fields these are by looking for the check marks next to the field names in the field explorer. If the database finds multiple records with the exact same values for every field, it will eliminate the extras and return only one of the duplicate records.

So here is the risk. Pretend that you have three records in the results that were mostly identical but had one field that was different, say 3 different timestamps. As long as the report doesn’t use the timestamp field then the “Select Distinct” will return only 1 of the 3 records. But as soon as someone decides to use that timestamp field, even if they just place it on a section, the SQL will see three unique records. Your report will then start to show all three records, even though you might consider them duplicates.

So if you are using “Select Distinct”, make sure you test any changes carefully, especially when they involve adding new fields to the report.









Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server