Left Outer Join on 2 tables from different databases 3

[johnstrang]

Hi,

I hope someone can help with this.
I need to link 2 tables from 2 separate Oracle databases held on Unix servers, which have the common field "Case ID".
In one database the field is a VARCHAR2(255) and in the other it is VARCHAR2(15) - please DON'T ask me why, I don't know but they are from 2 different vendors on 2 separate servers.

Anyway, the requirement is to get details of cases from Table 1, along with further details from Table 2(where there is a corresponding entry in Table 2). All I can get in the Visual Linking Expert is an Equal Join - which means that I don't get records from Table 1 where there is no corresponding record in Table 2.

This is using CR Professional 8.5 on my Win2K PC.

Any help or suggestions will be gratefully received.

Thanks in advance,

John

 

[JohnEck]

When you right clink on the link and open up the linkl options you can't set this as a left join.

 

[johnstrang]

JohnEck,

Thanks for the reply - no, I can't select Left Join - all the "SQL Join Type" options are greyed out.
Note that I am using "More Data Sources" / "Oracle Server" in the Data Explorer to locate the tables - does that make any difference?

John

 

[rhinok]

Unfortunately, this is a known limitation of not only Crystal Reports, but other tools such as Business Ojects Full Client. Many reporting tools don't like multiple data providers.

My recommendation is to create a View that accesses tables from each database.

 

[MattReed]

You could use subreports to get around this as well.

Matt

 

[LinPro]

Using subreports for this will take too long. The best solution is what rhinok said - create a view to handle this. Unfortunately, you will loose the indexes on the Case ID but you dont have a choice.

And the fact that you are using two databases (two schemes or databases?) this will slow down the processing.

Linpro

 

[Reebo99]

This is due to using the native oracle connection within Crystal Reports.

If you create ODBC connections this should help.

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[synapsevampire]

If you're able to write Views on either data source, than you might write an SP to use both datasources, this would prove fast and flexible.

And you should still be able to leverage the indexes, not sure what that was about...

The Subreport is the means you'd use if you can't do the above, and though performance will be worse, whether it's TOO slow is dependent upon number of rows and server performance.

There is one other solution, which would be to use an ADO recordset, this would allow you to write the SQL and pass it, though it's a pain in CR 8.5, it can be done.

-k