Unable to select Full Outer Join in Database Expert 1

[GaryCWL]

Backend Database: SQL 2000
Crystal Version: XI

Background Information:
I'm trying to create a report that shows me ALL the parts and various attributes between two databases whether or not a specific part exists in one and not the other. Currently, I've been able to somewhat achieve what I need by performing a Left Outer Join on one system and extracting the data I need; then performing a Left Outer Join on the other system and extracting the data I need. Then combine the two exports. But, since I need to do this on a frequent basis, I find it a lot of work to have to do the above steps when I think a Full Outer Join would solve my problem.

Problem:
I've created a view in two separate databases and joined the two views in Crystal (one being our product configurator and another being within our ERP system). However, when I try to join the two views, I only have the options to perform an Inner Join and Left Outer Join. I don't have the options to perform a Right Outer Join and Full Outer Join. Does anyone know why that is?

 

[TheBlondOne]

Hi there,

You hit all sorts of problems when your linking different databases together. You basically like you've seen loose most of the joining functionality. This is a common "issue" I say issue loosely as its always been like that in Crystal since I started using which was v8.

Is there no way you can create the views on the same database??

Have you tried creating a report and just use the fields which you require from one of the databases and then export this report as an ODBC and then select the other database which you are using to export this data too. In a sense this will create a table in the other database which you can query on. You should then be able to either create 2 views from the same database or just link up the tables, and you should have right outer join available to you. Not sure if you will have full outer join as again this (from my experiance) is determined by how the database is set up.

HTH

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[GaryCWL]

Hi Steve,

Thanks for your input. I'll have to give that a try or see what my limitations are with creating multiple views on one db.

The other problem that I have is these two dbs reside on two separate servers, one application is "on demand"/hosted by an external company and the other resides on our network. I'm going to have to get some expert DBA to help.

I'm not sure how much a Right Outer join would help since it would still put me in the same predicament buy extracting all the data from one db and not the other. I want to pull all data from both dbs whether or not there's any relational attributes to the same part.

Thanks,
Gary

 

[lbass]

If you could get the two tables into the same database, you could create a union query in a CR command that in essence combines the two tables into one.

-LB

 

[dwight1]

I am facing a similar kind of problem. I have two different tables and i want to Union those two tables. Can i do it in Crystal reports.

Thanks

Rishy

 

[lbass]

If you have version 9.0 or higher, you can use "Add command" (database->database expert->your datasource->add command). Enter the union query there. Are you familiar with how to set this up?

-LB