Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unable to Select Full Outer Join Between Databases

Posted on 2006-11-27
7
Medium Priority
?
287 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:Carla Romere
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 2000 total points
ID: 18022052
This is a limitation of Crystal Reports.  When you connect multiple databases--even when they are housed on the same server--you are limited in what you can do.  My recommendation is for you to create a View on one database that does everything you need within SQL.  You can then use this single View as your datasource for the report.

~Kurt
0
 
LVL 13

Expert Comment

by:crgary_tx
ID: 18022304
Can Business Views be used? I think by definition Business views integrate different datasources.

Thanks,
Gary
0
 

Author Comment

by:Carla Romere
ID: 18022346
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.
0
[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

 
LVL 101

Expert Comment

by:mlmcc
ID: 18022514
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
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 18023808
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
0
 
LVL 17

Expert Comment

by:MIKE
ID: 18024095
Can you DTS the data in...based on a SQL Script..so that ALL data is on the same server?
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 18025331
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
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question