Combining two databases with Crystal 8

[ziggs]

I have data in two databases with similar data that I want to combine (one is for employees alone and the other database is used by clients). Although each database has similar fields, they are not exact.

Database1 has a a case_number format like PD07001001 (based on julian date in the middle). The second database has a case_number format like 070001001, which I can convert easily to PD07001001. BTW, neither database will contain the same exact case number.

So, I need to tap both databases, create one report and produce the results. Can this be done?

If so, how would I linked both databases together, or do I even need to, to accomplish this?

 

[lbass]

In what sense do you need these two combined? What will your resulting report look like? In other words, do you really want corresponding fields to be treated as if they are one field? There is a way to do a union statement in the "Show SQL Query" area of this early version, but it's a little complex, so it would help to know what your goal is.

-LB

 

[ziggs]

Thanks for helping out. Our users are trying to avoid running two separate reports to compare data. They want to run one report only. Basically, I'll be pulling fields like Case number, type code, date/time field, and location. All will be sorted by date/time, so users can look at trends based on that sort.

For example, I pull in all Type A classifications:

Case # Date/time Type Location
PD07325001 11-26-07 0900 A 123 S Main St
070325004 11-26-07 1002 A 675 S. 10 St
PD07325017 11-26-07 1235 A 143 N. 7th St

I'll read up on the Union statement and see if I can pull it off. It's the linking of two databases that I'm confused on and don't know if linking is necessary or not.

 

[lbass]

Start by creating the report with only one table. Add the fields and the record selection formula. Then go to database->show SQL query. Let's say it looks like this:

Select employees.`case#`,employees.`type`, employees.`datetime`, employees.`location`
From employees
Where employees.`type` in ('A','B')

At the end of this query, add:

union all
Select clients.`case#`,clients.`type`, clients.`datetime`, clients.`location`
From clients
Where clients.`type` in ('A','B')

It becomes more complicated if you don't have corresponding fields in each table and you have to change the select clause of the original query. Note that you don't need to link the tables in this approach, as this approach merges the fields from the two tables into one field, labeled according to the fieldname in the first half of the query. Since the case numbers are different in the two tables, you can use the prefix that exists in the employees table to distinguish the record's original source.

-LB

 

[ziggs]

Yep, I don't have corresponding fields in each table. So, I'll try to make this easy. I created a report in each database with just one field (case number - Master_Incident_Number in this DB and DR in second). In the where statement "Call_Disposition" is similar to "NAT_CALL" in second One datbase is Oracle and the other is in SQL.

SELECT
Response_Master_Incident."Master_Incident_Number", Response_Master_Incident."Call_Disposition"
FROM
"System_Warehouse"."dbo"."Response_Master_Incident" Response_Master_Incident
WHERE
Response_Master_Incident."Call_Disposition" LIKE '02A00%'


UNION ALL


SELECT
PARSMASTER."DR", PARSMASTER."NAT_CALL"
FROM
"TIBURON"."PARSMASTER" PARSMASTER
WHERE
PARSMASTER."NAT_CALL" LIKE '02A00%'
ORDER BY
PARSMASTER."NAT_CALL" ASC