Background - There's an application built to use Crystal 11. The rpt file itself is pretty simple: based on one oracle database view, which includes two blob columns (one holding a photo, and another, an image of a signature). Prior to this we also have an older version of this same application, using Crystal 8. As far as I can recall when the *.rpt file was converted, nothing special was done. It still uses the same single oracle database view.

My DBA, using v$sql, noticed that when we run the report it now queries the database view THREE TIMES. Prior to this, it was only sending ONE query. These TWO extra queries are being done to the same view as the first, but without a whereclause, and only looking for those respective individual blob columns.

This was NOT how it behaved when we were using Crystal 8. He verified this.

Crystal 8 only queries the view once, and gets all the columns it needs.

Crystal 11 also sends this same query, which itself also includes the blob columns.

There is no need for Crystal 11 to send those additional two queries.

No formulas in Crystal 11 rpt file have been added. Both Crystal 8 and 11 versions of that rpt file have only the record level selection formula. I searched on each of those blob objects in design layout to confirm it was getting those from the same view, etc.

Playing with it further I did the following:
* I got rid of all the database objects in design layout.
* I added back two text columns (no blob columns being used)
* The DBA confirmed only ONE query to the view was happening.

* I then added back the photo blob.
* The DBA says TWO queries are showing. The previous one mentioned, and one for the photo blob.

* I then added in the same photo blob so that there is a duplicate photo on the screen.
* The DBA says no change. Still only shows TWO queries.

* I then added the OTHER blob for the signature image.
* The DBA says now he sees the THREE queries.

What is going on!?

Why didn't Crystal 8 behave this way? Is this a known bug, and how does one get around it without having to redesign the entire report another way?

Other info:
I verified that the rpt file references only one database view.

I verified there is only one formula. This only exists at the record selection level. It contains no references to photo blob.

One thing I did find interesting however was under the “Report Fields” list in the formula editor I’m seeing all the fields used in the report designer/formula, with the exception of Photo and Signature, which are not listed there. This leads me to believe that Crystal handles these separately.

There are no sub-reports being used.

Edited by patilj1975 - 24 Mar 2015 at 3:29am