So here is an odd bug I found. It happens every time in Crystal versions 9, 10 and 11(XI) but also happens in some reports in newer versions. The bug only affects SQL Expressions that contain a correlated sub-query. For example this expression works in both CRv8.5 and CRv12 (2008) but fails in all of the versions in between:

(SELECT Min (CAL.ONDATE)
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo)

The expression is designed to show the first appointment in the Cal table for each contact in the report. Using a SQL Expression avoids duplication I would normally get by adding the Cal table to the report directly. Run this expression in CR versions 9, 10 or 11 and you get this error:

Error in compiling SQL Expression.
The multi-part identifier Contact1.AccountNo
could not be bound.

At first I thought this error was coming from SQL. But as a test I took a v12 report that had this expression and opened it in v10. The report ran fine. But if I then try to edit the SQL expression or even check it, I get the error above. At that point the report will no longer run in v10 no matter what I do. My only option is to close it without saving changes and reopen it – then it works fine again. This tells me that the SQL is fine because it isn’t the DB that rejects it. The error only occurs when CR tries to validate the expression. The validation only happens when the expression is being modified and saved, which is why the report will run when first opened. But once the validation has failed the report can’t be refreshed, so the SQL expression must be marked as bad until it can pass the validation.

A few more experiments showed that this error is somehow related to using aliases. If I remove all alias references the problem goes away. This expression works in all CR versions:

( SELECT Min ( ONDATE )
FROM GM6.dbo.CAL
WHERE ACCOUNTNO = Contact1.AccountNo )

It is curious that the alias of the CAL table causes the field from the Contact1 table to error out.

But here is the really odd part. Another way to fix it is to put a SQL comment line at the beginning of the expression. If the comment includes the name of the field mentioned in the error message, then that somehow makes it work, even with aliases. So the following expression also works in all versions:

(
/* some words accountno and some more words */
SELECT Min ( CAL.ONDATE )
FROM GM6.dbo.CAL CAL
WHERE CAL.ACCOUNTNO = Contact1.AccountNo )

The only word necessary in the comment is the word ‘accountno’. If I delete that word or misspell it, I get the original error. Clearly the comment doesn’t make a difference to the database engine, but it must have some effect on the CR validation. This last behavior was discovered by Bob Antaki, a developer with the Bay Area News Group in California.

So if you have struggled with adding SQL expressions like these to your reports, hopefully this will help. If you want to learn more about using SQL in Crystal Reports you can read my Expert’s Guide to SQL Expressions, Options and Commands, or you can schedule a session with me to go over some of these concepts.