Linking DBs in CR 11 1

[madsstiig]

Hi.

I just love this site...!
What I don't love is our IT department who decided that the databases I use to extract some statistics no longer need to have the same data form in the only two fields (one from each DB) I use to define my reports.
I work as a system admin on a hospital and the two DBs are one for image data and one for data concerning the booking for these image data.
The workflow is started with someone creating a request that results in a booking in the first DB (RIS database). When the patients get their examinations done images are sent to the second DB (PACS database).
Each exam is identified by a unique(!) accession number. Except that recently this number gets a prefix ('DKRIG') in the second DB.
When we work with CR I know that it's possible to define that we only ned e.g. the last 8 characters from a certain field but do we have the same option when linking the databases?

Best regards, Mads Stiig

 

[lbass]

Two possibilities come to mind, although someone else might have a better idea:

1- Use a SQL expression in the database with the longer number to remove the first five characters. Depending upon your DB, it might be left() or its equivalent. Then use this expression to link one database to the number in the second database (that you have added in a subreport) as a subreport link.

2A- If you are able to use the same driver for both DBs, try creating the report using a command where you again use a form of the left function to link one table to the other within the command.

2B- Or, try using a union all statement where you use the shortened field as one of multiple fields selected from the DB to merge the fields from the DBs into one table. For union statements, the corresponding fields must be in the same order, with the same datatype and format. You can use the word “null” (without quotes) to maintain the correct order for fields that don’t have a corresponding field in the other DB. If you need to know which DB a particular value is coming from, add a “type” field to act a label.

-LB

 

[madsstiig]

That was a great idea, LBASS but alas I only have read-only access to the databases.
Though you gave me an idea. Since I only need to get data from at a max one month's exams I could do a report with data from the images from that month containing the necessary fields, save this as an excel sheet and then use this sheet as my second database - with a little 'cleaning up' I can get rid of the prefix.
Thanks for the answer

 

[kray4660]

The suggestions that Lbass made do not require any other access than read only. A knowledge of SQL statements would benefit your issue. Unfortunately not all databases uses ANSI standards, so you may need to do some research on the LEFT function (or it's equivalent) for your database.