Problem setting WHERE criteria in CR 8.5

[Roebuck]

I have a problem getting a report to return the correct results in Crystal Reports 8.5 when using DISTINCT.

I have set up the report to access the required tables and the SELECT...FROM... statement is correct.

For example:
SELECT DISTINCT A.1, B.2
FROM A LEFT OUTER JOIN B ON A.1 = B.1


However, when I use the Select Expert to add criteria to the where clause, i.e. WHERE B.5 = 42, Crystal automatically adds this field into the SELECT statement.

For example:
SELECT DISTINCT A.1, B.2, B.5
FROM A LEFT OUTER JOIN B ON A.1 = B.1


I don't want this value returned as it prevents the DISTINCT from working correctly. Is there a way of removing it, stopping it being added, or another solution?


Thanks in advance.

 

[GJParker]

What version of CR are you usinf ?

If you are sung CR9 or 10 you will need to change your data source to a command object, this will allow you to enter the SQL as you wish.

Versions prior to this will allow you to edit the SQL using Database menu -> Show SQL Query.

This said, I don't think this is your problem as

SELECT DISTINCT A.1, B.2, B.5
FROM A LEFT OUTER JOIN B ON A.1 = B.1
WHERE B.5 = 42

Will return the same distinct records as

SELECT DISTINCT A.1, B.2
FROM A LEFT OUTER JOIN B ON A.1 = B.1
WHERE B.5 = 42

because B.5 can only ever be 42.

The WHERE clause has nullified the LEFT OUTER JOIN so any records with null values will not be returned.

HTH





Gary Parker
MIS Data Analyst
Manchester, England