Unions?? Joins?? Links?? Oh My

[Sweetworks]

CR 8.5
Using ODBC to connect to Oracle 8.1.7.4

I have two identical database's. Each for a Different Sister Organization.

I want to create one consolidated sales report directly via Crystal, with out using access.

Database 1 has these Tables I want to use
Customer
Receivable
Receivable_Line
Database 2 has the same Tables
Customer
Receivable
Receivable_Line

How can I have
Database_1.Customer and Database_2.Customer
be 1 Table for Crystal to then reference for the report.

Obviously I would want the same for
Database_1.Receivable and Database_2.Receivable
Database_1.Receivable_Line and Database_2.Receivable_Line

 

[pgtek]

hi
just used alias to rename the table
click on databse and the set alias
cheers

pg

pgtek

 

[pgtek]

hi
also link them by
Database 1 has this Table alias main use
main.Customer
main.Receivable
main.Receivable_Line
Database 2 alias slave link the field to each one
slave.Customer
slave.Receivable
slave.Receivable_Line


pgtek

 

[synapsevampire]

I'd create a View on the database which uses a Union.

Not sure how Crystal will react to trying to do this directly from within Crystal, although you could use an ADO connection and paste in the SQL.

It would look something like:

select A.field1, B.field2, C.field3, '1' DBSource
from
db1.Customer A,
db1.Receivable B,
db1.Receivable_Line C
where
...
UNION ALL
select A.field1, B.field2, C.field3, '1' DBSource
from
db2.Customer A,
db2.Receivable B,
db3.Receivable_Line C
where
...

Now you have the data in one table and an identifier as to it's origin.

Since it's in a different database, Crystal might puke if you try to do the Union from within the Database->Show SQL Query, but that's how you can Union when within 1 database.

-k

 

[synapsevampire]

Ooops, change the value of DBSource:

select A.field1, B.field2, C.field3, '1' DBSource
from
db1.Customer A,
db1.Receivable B,
db1.Receivable_Line C
where
...
UNION ALL
select A.field1, B.field2, C.field3, '2' DBSource
from
db2.Customer A,
db2.Receivable B,
db3.Receivable_Line C
where