Trying to link two databases with a similar, yet different foreign key 1

[ziggs]

We have two applications within two separate departments in our organization. When an incident comes in, it's assigned an Event Number like so, 2002001001 (2002 is the year, 001 is the julian and 001 is the first event of the day). Now, the second department gets the same number, but they enter the incident in a different application. Thus, there number for the same incident looks like this, 020001001. 02 for the year, 0 for no good reason, 001 for the julian, and 001 for the first event of the day.

Now, both databases are in Oracle. What I'm trying to do is make a report that pulls data from both databases for an event number. However, as you can see, there is no common key, only a similar key. I know how to use a formula using left, mid, right to create what I want to make the numbers look the same. However, I'm unsure how I can link the two databases since I can't see how I can link a formula in the linking expert.

Any workarounds to accomplish what I'm trying to do? TIA

 

[dgillz]

Create a report from either table with a formula field in it that expresses the date in the same way the other table does, then create teh report from the other table.

Use the first report as a linked subreport, and use the formula field to link the two together. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com