Date Parameter and duplicates

[micro2005]

Hi everyone,
I am using CR 10 and SQL Server 2000. The report is based on a view and one table joined on on complaintid. When I added the date parameter for range (start and end date), all the rows are repeated 3,4 or 5 times.

Here are the details of my SQL:
SELECT "cr_activeemployee"."complainant", "cr_activeemployee"."caseid", "cr_activeemployee"."office", "cr_activeemployee"."formal_filed", "cr_activeemployee"."written_note", "cr_activeemployee"."fad", "cr_activeemployee"."investigation_requested", "cr_activeemployee"."roi_issued", "cr_activeemployee"."hearing_requested", "cr_activeemployee"."hearing_End", "cr_activeemployee"."mixedcase", "cr_activeemployee"."remanddate", "cr_activeemployee"."filesentdate", "cr_activeemployee"."eeodecdate", "cr_activeemployee"."fadreqdate", "cr_activeemployee"."acceptance", "cr_activeemployee"."dismissal"
FROM "icomp_2911_usda"."dbo"."cr_activeemployee" "cr_activeemployee"
ORDER BY "cr_activeemployee"."office"


SELECT "complaintevents"."startdate", "complaintevents"."complaintid"
FROM "icomp_2911_usda"."dbo"."complaintevents" "complaintevents"
WHERE ("complaintevents"."startdate">={ts '1999-05-13 00:00:00'} AND "complaintevents"."startdate"<{ts '2005-05-14 00:00:00'})

My parameter is of type date and here is my record selection:
(not isnull({cr_activeemployee.formal_filed}))
and
(isnull({cr_activeemployee.fad}))
and
({complaintevents.startdate} in {?Start Date} to {?End Date})

Any ideas why this is happening?

 

[Naith]

Sounds like you're getting a cartesian product because you're not joining properly.

Is complaintid really the only field you're supposed to link on?

Naith

 

[micro2005]

Yes, its the unique identifier for the complaintevents table.

 

[Naith]

Take Crystal out of the equation for a second.

When you write this code directly against the database, linking your table with your view, in the same way you've mentioned, what happens?

 

[Madawc]

If you can't fix the join, group the duplicates on the data they are duplicating. Suppress details and use group header or footer as if it were a detail line.

I've had to do this a lot on our database, which is a copy of a mainframe system and full of valid one-to-many links.

Madawc Williams (East Anglia, UK)

 

[micro2005]

After reading Naith's response, I tried to link the table and the view on a different field. I then modified the record selection statment and that solved the problem. Naith, thank you very much for your respone. Madawc, I will also keep your suggestion in mind for future use.

Thanks!