Link field restrictions when linking different ORACLE databases

[eaglet]

I have seen documentation that states that if you are linking tables from two different ODBC data sources (mine are both Oracle), the link fields can only be STRING field types. This appears to be the case with CR7, CR8.5, and CR9. Does anyone know if higher versions have corrected this problem? I have come up with a work-around where I link to another field in the first data source that has an acceptable string field alternative that I can use to link to the second data source. But this adds one more table to the mix and slows down processing. I have also been able to use subreports, to get around this. However, I would like to think that BO would be addressing this seemingly inane join restriction. Does anyone have any experience with this problem, know if it is fixed in higher versions, or know some other way to use a numeric field to join tables from two different data sources? Thanks!

 

[synapsevampire]

You can copy and paste SQL into Crystal Reports, even back to version 8, so there's no reason for subreports. CR 9 and above make it even easier by using the Add Command.

Another common alternative to using Crystal as your database engine is to LINK (not import) files into MS Access, build a query and expose the query to CR.

Try to avoid subreports and wherever possible build Views or SPs.

-k

 

[eaglet]

Thanks for your suggestions. I had not thought of directly pasting in the SQL statement. I usually try to avoid this approach as many of the reports I create are sent to users who don't know SQL and may wish to make simple modifications to the report from time to time.

Also, we have recently moved some of our tables to a separate data base and our users are suppose to change their reports to link to the new database. Unfortunatley, the main field that is used for linking, for 90% of our joins, is numeric.

I have also used views (which is a good suggestion) in the past to get around this problem. I was just hoping against hope that the problem might be resolved in CR 10 or 11 or that there might be a patch for it floating around somewhere.

 

[Turkbear]

Hi,
Is there a reason you are not using Native Oracle connectivity..I do not believe the restriction of link types applies with that connection..At least, I know you can link by a Date field..

 

[eaglet]

I am not very knowledgeable about the technical connectivity aspects, but I believe I am using Native Oracle connectivity. The ODBC driver than I am using is ORACLE73 v 2.5 (version 2.05.03.01) and the file name is SQO_3273.DLL. Is this considered Native Oracle?

Thanks

 

[synapsevampire]

ODBC isn't native, and Turk is correct that Native is the way to go. The Oracle ODBC driver isn't suggested with Crystal, you should be using the Crystal supplied ODBC driver if you have some overiding reason to use the slower method.

Your assumption that pasting SQL disallows modifications in the report is incorrect as well.

Ultimately I would build Views/SPs on the database as they lend themselves well to reusability maintenance.

-k

-k

 

[eaglet]

Thanks for your insight. I'm not exactly sure why we are using the Oracle ODBC drivers instead of CR, but I will experiment with the CR driver and see if it works.

Yes, I agree that pasted SQL code can be changed, but I'm not sure that our users, who dabble in Crystal, are sophisticated enough to monkey with that code. And views are good (and maintainable) and much faster than having Crystal handle the joins. I've had to use them for really long running reports.

I'll let you know if changing the drivers works.

Thanks!