SQL query SELECT DISTINCT how??

[KidFix]

I've got some duplicate records in my db and need to filter them out of my report. I've tried putting SELECT DISTINCT into my SELECT statement but crystal doesn't seem to like this because it always reverts back to SELECT. How can I filter out duplicate records??

Thanks in advance

 

[francisst]

Under the Database menu in Crystal Reports Designer there is a Select Distinct option.

 

[synapsevampire]

This depends on your version of Crystal.

If you're using an older version, then you have to use a trick:

Copy the entire SQL statement of the report to the windows clipboard.

Paste the copied SQL Query at the bottom of the existing query.

Add a 'UNION' clause between the two SQL statements.

Modify the WHERE clause of the first SQL to say 'WHERE 0=1'.

If the SQL does not have a WHERE clause, add one.

Note: The 'WHERE 0=1' makes the first SQL fail on each record because 0 will never equal 1. The first SQL statement will not select any records.

Modify the second SQL to read 'SELECT DISTINCT'. Since this is the second SELECT statement in the SQL, Crystal Reports will allow you to modify it and add the 'DISTINCT' word.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com