Data from Multiple locations

[lynchg]

I have Crystal Reports 9 Full Developer Edition. I have been asked to create a report that is supposed to take the data (a list of patient appointments), from a Lotus view, and then query against a DB2 database to create a report to send back to the doctors' offices. I got started on this project before all of the tables were created in DB2, so I created a mini version of the database in Access based on the DB2 data model.

The report has been created and all of the SQL logic has been worked out, I even began translating the SQL into DB2 syntax. I had anticipated that the most logical approach would be to have all the SQL in DB2 and have a link of some kind between DB2 and Lotus so that the view could be incorporated in to the views within DB2. I just found out that that is not possible, our DB2 people don't know how to do that and don't have time to investigate.

So now I have been told that I should get all of my SQL into Crystal and pull my data from Lotus and DB2. To accomplish this I would need to write some queries in Crystal joining the Lotus view to DB2 tables. The posts I have read so far about getting data from separate locations are not very encouraging.

Here is an example of a query that would need to access both databases. I have prefixed the table/field names with the source of the data (DB2, LOTUS), for that field.

SELECT DISTINCT DB2.MEMBER_LOOKUP.CORP_MBR_ID, LOTUS.APPT_LIST.AppointmentDate
FROM LOTUS.APPT_LIST INNER JOIN DB2.MEMBER_LOOKUP ON (LOTUS.APPT_LIST.MemberNumber = DB2.MEMBER_LOOKUP.MBR_DEP_NBR) AND (LOTUS.APPT_LIST.SubscriberID = DB2.MEMBER_LOOKUP.MBR_SUB_ID)

Any help would be appreciated, thanks.

 

[synapsevampire]

You can add in both datasources and join them in Crystal.

But you might consider "linking" (not importing) the datasources in Access and building the query in there as it has more flexibility and better performance. Then use the Access query in the Crystal Report.

-k

 

[lynchg]

The problem is that the Lotus view is just a list of the patients and their appointments. I need to go thru a rather extensive series of queries to finally arrive at the data set needed to feed the report.

Access is not an option, too feeble for the amount of data involved. We only started with Access to get a head start on developing the query logic and report development, at that time the DB2 database was still under construction.

I think our best shot would be to send the data from Lotus directly into a table in DB2, house all of the SQL in DB2 views, and populate the report directly from views in DB2.

If anyone knows exactly how to send data from Lotus Domino to a mainframe DB2 database, that would be very helpful, I am not finding encouraging news regarding solutions with Crystal in any documentation or from posts that I've read here.

TIA

 

[synapsevampire]

I agree, that DB2 is best, but you'd just stated that you couldn't do so, hence the option.

As for Access being too feeble, it's much less so than Crystal.

This might help:

http://www.cs.duke.edu/~junyang/cps196.3/faqs/sql.html

There are utilities also:

http://noteslinks.com/index.php?cat=74

-k

 

[lynchg]

I haven't given up on the DB2 solution, I got shot down by our DB2 people but I am seeking help from outside. To me it makes the most sense to have all of the SQL sitting with the data and then just send the results to the report instead of running back and forth across the network every time a query is invoked.

Thanks for your advice.