Links between 2 different Data Sources

[bpvsc]

I am trying to create an exception report that will return records from 1 data source that are not in the second data source such as case numbers. I am trying to link 2 string fields 20 characters long. I am getting the message" The specified fields may not link successfully due to different type". But they are the same. My data sources are an Oracle database and a DB2 data source.
Any suggestions out there?
Thanks

 

[lyanch]

I have this problem as well. I appears that oracles' varchar2 does not map to the standard varchar. I have not been able to get this link to work. Although this probably won't work for you, my work around has been to use subreports which link string to string.

Lisa

 

[bpvsc]

I have been partially successful creating a subreport. However, I need to only see the exceptions from what is in one datasource and not in another and vice versa for auditing purposes. When I try to accomplish this select statement I cannot see the primary source as an option for the select statement. Any ideas?

 

[Naith]

If you're able, you can create an Oracle view based on your Oracle data source. The only difference you make in your view is converting the varchar2 datatype to varchar.

There's actually no difference between either of the two datatypes as far as Oracle is concerned; it's just best practice to use varchar2 in case future releases of Oracle decide to attribute the varchar datatype to something else.

Naith

 

[dgillz]

The message you got was a warning, nothing more, nothing less. I have successfully written reports many times with multiple data types.

In terms of your subreport, I do not beleive you can do a not equal to link on a subreport, nor can you do it on links between tables.

Stick with one report, link your two tables with an equal to join on the common field(s) and write a selection formulas as follows:

IsNull({TableB.Field})

This will effectively return all the records that exist in table A but are not to be found in table B.

If you want the opposite of this, go to the visual linking expert and reverse your links, and change the selection formula to look at the field in the other table. TYhis will return records in Table B not in Table A.

Let me know if you have any questions.

Don Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[lyanch]

Don,

I too went blithly by the warning, ignoring it. Ususally the join will still work just fine. In this case it doesn't, nothing is returned from the second DB.

Lisa

 

[Naith]

I can't admit to knowing how db2 does it's thing, but I would be wary about pairing up a datatype from another db to a different Oracle datatype and expecting it to be dependable.

The way Oracle stores field data in a varchar2 is a bit different to how another database, like Sybase for example, would store it's data. (e.g. Sybase right trims a nullable character field, while Oracle would actually store it as null. So, when you pair up the two datatypes, Oracle reads the right trimmed field Sybase varchar field as ' ' rather than '' or null.)

Just my twopence. Ignore my rambling if it's not relevant.

Naith

 

[bpvsc]

I agree that is probably what is happening but is there any way around it to create the report? Is it possible to use a subreport and create a statement excluding everything that matches? If so how would it be written and executed?
Thanks for you advice.
B