Crystal Reports not returning values correctly based on Database view

We are using Crystal Reports XI to connect to an oracle 9 database.

We are using a view which basically the following:

Create or replace view transcript_list (ID, completion_date) as
Select ID, completion_date from transcript

When we run this view against the database in TOAD  106000 records are returned.
When we run a crystal report using this view, 106000 records are returned.

If we then change the view in the database to be the following
Create or replace view transcript_list (ID, completion_date) as
Select ID, completion_date from transcript where transcript.completion_date >='01-jan-2007'

When we run this second view against the database in TOAD only 16000 records are returned.
If we run the crystal report again based on this second version of the view we still get 106000 records.

We've tried another where clause e.g  where ID ='x' value and the view is correctly reported in Crystal. So there appears to be something different about the date in the where clause that crystal is interpreting differently to TOAD.

Values stored in the completion_date field in the transcript table are formatted as date time and we've also tried the following where clause round(transcript.completion_date) >='01-jan-2007' but we still get 106000 records via crystal and 16000 via TOAD.

Any ideas on what is happening would be greatly appreciated!! We need this view to only return the 16000 records to the report and not the full 106000!
Who is Participating?
frodomanConnect With a Mentor Commented:
Try changing your Crystal formula to:  ... to_date('01-jan-2007','dd-mon-yyyy')

I believe Toad may translate the text value to a date type for you, but the db driver Crystal is using isn't doing that.

If that doesn't work, let me know if there's any possibility of null values in this date field - if so you have to explicitly check for them or Crystal doesn't give accurate results.

Have you used the 'verify database' option to make sure the report is picking up the latest version of the view?
hcshostingAuthor Commented:

yes Ive tried this and the it comes back database is up to date but doesnt have any impact of the records returned.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

try ( if using visula studio )

Right click on report select Design---> Default settings.....> Reporting
now check opton "Discard saved data when loading reports"

try ( if using Crysatl report designer)

File--->Options---> Reporting

now check opton "Discard saved data on Open"

Hope it will work
hcshostingAuthor Commented:
Thanks for the suggestion.
We are using crystal report designer so i tried the second option but its still bringing back all the records.
have you also tried  by unchecking "Saved Data with report" option?
hcshostingAuthor Commented:
yep, i think its more to do with the where clause having a date. If i change the where clause to be based on the ID field instead the records are restricted in crystal.but just cant work out what?
hcshostingAuthor Commented:
Thank you! that worked.. Im now only getting the 16000 records via crystal.
There is also a possibility of null values in this date field so would appreciate your advice on how to explicitly check for these as well?
Sure try this:

...where transcript.completion_date is not null AND transcript.completion_date >='01-jan-2007'

Unless you want to include the null values in which case:

...where transcript.completion_date is null OR transcript.completion_date >='01-jan-2007'
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.