Unable to Select Full Outer Join Between Databases
I have a report based upon four views from one database and one view from another database (housed on the same server). I need a full outer join between one view and each of the other 4 views. That isn't a problem with the four views within the same database, but I can't get a full outer join between the main view and the one view that is in another database. I can get the left outer join, but not a full outer join. If I could get the full outer join to work, I think the report would run faster.
View 1 - Full Outer Join View 2 (same database)
View 1 - Full Outer Join View 3 (same database)
View 1 - Full Outer Join View 4 (same database)
View 1 - Left Outer Join View 5 (different database)
I've checked the field types and they match (all varchar) - however, some of the fields are longer in view 5.
Any ideas why this isn't working?
Thanks in advance.
View 1 - Full Outer Join View 2 (same database)
View 1 - Full Outer Join View 3 (same database)
View 1 - Full Outer Join View 4 (same database)
View 1 - Left Outer Join View 5 (different database)
I've checked the field types and they match (all varchar) - however, some of the fields are longer in view 5.
Any ideas why this isn't working?
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay - I'm showing my ignorance here - I don't know what Business Views is...
Each view is pulling such very specific information and I don't know how I'd limit that from within CR.
For instance: 3 of the views are actually based on the same table on the sql-server but are each limiting the information they pull. Here is a brief example:
View #1 pulls all current inventory from Table 1
View #2 pulls only the product SOLD within the last week from Table #2
View #3 pulls only the product we ran in the last week from Table #2
View #4 pulls only the product we received in the last week from Table #2
View #5 pulls only the products we expect to receive in the next week from Table #3
The report is based upon location/product/form and each of the views is joined upon that same information.
The customer wants to see each location and for that location to see what amounts to a crosstab report showing 5 different numbers for each location/product/form combination. Material received, current inventory, material sold, material ran, and material expected.
Table #2 Table #1 Table #2 Table #2 Table #3
Location A Received Current Sold Ran Expected Anticipated (current + expected)
Product A Form A 42000 1468000 0 126420 120000 1588000
Product A Form B 0 250000 20000 175435 84000 334000
If there is a way to limit each column's data by very specific criteria, I could do the entire report with only 3 views instead of 5.
Each view is pulling such very specific information and I don't know how I'd limit that from within CR.
For instance: 3 of the views are actually based on the same table on the sql-server but are each limiting the information they pull. Here is a brief example:
View #1 pulls all current inventory from Table 1
View #2 pulls only the product SOLD within the last week from Table #2
View #3 pulls only the product we ran in the last week from Table #2
View #4 pulls only the product we received in the last week from Table #2
View #5 pulls only the products we expect to receive in the next week from Table #3
The report is based upon location/product/form and each of the views is joined upon that same information.
The customer wants to see each location and for that location to see what amounts to a crosstab report showing 5 different numbers for each location/product/form combination. Material received, current inventory, material sold, material ran, and material expected.
Table #2 Table #1 Table #2 Table #2 Table #3
Location A Received Current Sold Ran Expected Anticipated (current + expected)
Product A Form A 42000 1468000 0 126420 120000 1588000
Product A Form B 0 250000 20000 175435 84000 334000
If there is a way to limit each column's data by very specific criteria, I could do the entire report with only 3 views instead of 5.
As Kurt pointed out you will need to do this in the database as Crystal cannot handle it.
Can you add a linked table to link table 3 into the other database?
The other way to do this is to uswe a subreport to provide the data from table 5.
mlmcc
Can you add a linked table to link table 3 into the other database?
The other way to do this is to uswe a subreport to provide the data from table 5.
mlmcc
Business Views are a semantic data layer you create between the User and the Database. If you're using Crystal Reports 10/Crystal Enterprise 10 or crystal Reports XI/BusinessObjects Enteprise XI then you can create Business Views. I opine, however, that this would add an unnecessary layer of configuration and maintenance to your report. A simple database view would be the easiest way to go.
~Kurt
~Kurt
Can you DTS the data in...based on a SQL Script..so that ALL data is on the same server?
Why would you DTS data in if you can simply create a view that joins the data from both servers in a single place? By doing so, you'd now have data in two places and would have to maintain it via automated DTS packages. It would also mean that the data isn't 'live', which would be fine in a true data warehouse environment.
A simple view would look like this:
~Kurt
A simple view would look like this:
~Kurt
Thanks,
Gary