CR XI left outer join not producing correct query

[saied]

In CR 8.5, I had a report that left outer joined 6 tables together.
5 of them reside in the same database and 1 resides in another database. I will refer to tables as A, B, C, D, and E. The query produced by 8.5 using left outer joins was:

select distinct
A.Field1 B.Field1, C.Field1, D.Field1, E.Field1
FROM
DB1@Server:owner.A A
outer DB1@Server:owner.B B
outer DB1@Server:owner.C C
outer DB1@Server:owner.D D
outer DB2@Server:owner.E E

CR XI produces an entirely different query:

select distinct
A.Field1 B.Field1
FROM
DB1@Server:owner.A A
outer DB1@Server:owner.B B

select distinct C.Field1
From C

Select distinct D.Field1
From D

Select distinct E.Field1
From E

It is only outer joining the first two tables and then seperating the rest into seperate queries. Is this a bug within Crytal XI? Does anyone have a solution?

Thanks,
Saied

 

[Madawc]

I had a similar problem after we upgraded from 8.5 to 10. The new version of Crystal interpreted the links differently, and was maybe more accurate.

Unless someone else can offer a clever solution, you may have to start again and re-write the report so that it does the job in Crystal XI.

Do you have SQL? And can you write things in it? Stored Procedures are much better in the higher versions, with the output being linkable with ordinary tables.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[cmmrfrds]

An alternative, which will work with any version of Crystal, is to push the joining down to the database engine. If both remote databases are Sql Server then it can easily be done with a Command Object in your own SQL, or in a dynamic View on the database engine. If one of your databases is Sql Server (most of the tables) and the other is Oracle then there are a number of ways to link the Oracle table within Sql Server so that you only connect to the Sql Server database. If you explain your environment and your level of SQL expertise, then maybe we can come up with some recommendations.

 

[synapsevampire]

An example of a query across databases is:

select A.field1, B.field1
from Database1.dbo.Table1 A
inner join Database2.dbo.Table2 B
on A.LinkColumn = B.LinkColumn

This assumes that you mean different databases, not servers.

-k