Crystal Reports 9.0 and Oracle Left Join 2

[toekneel]

I have discovered that our Oracle databases do NOT allow me to do left outer joins in Crystal Version 9.0. The same reports run fine in Crystal 8.0. Reports hitting the same database with equal joins run properly in 9.0.

I am getting the following error messages:
"The database file xxx has changed. Proceeding to fix up the report!" This occurs for each database file, although the database owners indicate no changes have been made.

"Failed to open a rowset"

"Failed to open a rowset. Details: 42000:[Oracle][ODBC Oracle Driver][Oracle OCI] ORA-00933: SQL command not properly ended."

I remember seeing a similar problem three years ago with the Merant ODBC driver, but in this case, it does not seem to be driver related, since it works in earlier versions of Crystal Reports.

Any one else run into this problem?

Tony

 

[Thadeus]

Toekneel,
First off, well documented, thanks. Secondly, a clarification...
A) Is the Join happening in the Database Expert / Linking?
B) Is the Join being done directly through SQL statements?
C) Is the Join happening in Oracle and you are hitting the View?

If you are not using an SQL statement directly, I would recommend that you attempt that as a test...

Thadeus

 

[toekneel]

The join is being made in the database linking expert. I don't have the SQL tool loaded, so I don't know if I can do a direct SQL statement. Is there a way of doing this in the designer? I know I can view the SQL statement, but I cannot modify it at that point.

 

[synapsevampire]

Try using the Crystal supplied ODBC driver, the error indicates that you're using the Oracle ODBC.

-k

 

[toekneel]

This does work when loading the CR Oracle 7 driver. I will be running some tests to see if it messes up other applications that need to use the same ODBC connection.

Thanks for the input!

Tony

 

[Thadeus]

Thanks SV, this is an area I'm not as well versed in....

However to answer the How do I enter the SQL directly question:

To random on-lookers, this is for CR9

OK, Here's how I do it...
Open a direct connection in the DB Expert to the Oracle server. Don't choose any tables yet. Click on Add Command which appears immediately under the DB's name. This opens the Add Command To This Report dialog.

Enter your SQL into this box and either hit the OK or the "Add to Repository" and the OK....

Thadeus

 

[IdoMillet]

Crystal 9.0 has a known issue with Left Outer Joins.

The problem and the solution are described in:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/cr9_left_outer_join.pdf

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[toekneel]

Thanks! I did not find this when searching their knowledge base. This looks like it may keep our company from pushing Crystal out corporate wide.

 

[IdoMillet]

The solution (registry) is not that difficult...

You may also want to contact Crystal Decisions Tech Support to find out if a later release of Crystal 9.0 has incorporated the needed registry settings in the install.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[toekneel]

Correcting registry setting on one pc wouldn't be a great deal, but with the number of databases involved corporate wide and state-wide, and the number of users impacted by it state-wide, it could possibly be a multitude of registry settings on each pc. This could prove to be more expense for us than it is worth.

 

[lyanch]

Only if you are actually distributing the actual reports. If you are using enterprise or some other distribution method you will only need to fix the registry of the server actually running the reports.

Lisa

 

[toekneel]

Correct- but we are not using Enterprise at this time.

 

[IdoMillet]

If the PC users have permissions to update the registry, the fix is as simple as double-clicking a little *.reg file.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com