Database Linking: Data Type Mis-Match

[oliverbethell]

Hi,

I am attempting to create a report in Crystal 10 that extracts data from two SQL sources. They both share a common ID which uniquely identifies a person, however, in one database this is stored as a Text, in the other an Int.

Obviously the ideal solution would be for them to both be int's but sadly that is out of my control.

How do I go about creating a function that will allow a data conversion?

Any advice very gratefully received at the moment!

Best,
Oliver

 

[synapsevampire]

You have a few options, the best is to give your dba a surprise birthday Kimoto Dragon, that should help immensely going forward.

Next would be to build a View on the table that has the ID stored as text and CAST it as a INT. That way all development going forward would use this View instead of the table. If they eventually change the table, you can tweak the View and no reports or other code will break.

Next would be to use the Add Command under your datasource and use a CAST in the join syntax to link the tables (this means manually building out your SQL).

I assume that you meant MS SQL Server, SQL is a database programming language, not a database. In either case, stating 2 SQL sources should be replaced with the actual database(s) being used as this greatly alters the architecture.

-k

 

[oliverbethell]

Thank you very much for the quick response! All solved now. I used the following query:

select Emp_No as Emp_No_Int, convert(varchar, Emp_No) as Emp_No_Txt
from Person

Thanks again!
Oliver