Linking / Joining Tables with differing data types 2

[GaryCWL]

Backend: SQL Server 2000
Crystal Version: XI

I need to link two tables Cart.ItemID with Design.Serial_Number; however, they Data Types differ. Cart.ItemID is a Varchar and Design.Serial_Number is an Integer.

Is it possible and if so, how do I do link/join the two tables in Crystal?

When I try to link the two in the Database Expert, I receive a "Data Types are not compatible" message.

 

[synapsevampire]

Yeah, that's a big problem, and my first suggestion would be to ask the dba to create a View of one of the tables that does a CAST or CONVERT against the table so that you don't have to code around their inconsistent data typing (I wouldn't state it so gently though, and I would not so secretly wish that the dba would get his fingers caught in the paper shredder...).

Probably the best solution would be for you to use the Add Command listed under your connection and paste in the appropriate SQL, something like:

select <field list>
from <tables>
where CAST(table1.int as varchar(20))= table.varcharfield

Within Crystl the solution is to use a subreport, which is slow and kludgy.

In that instance you would create a formula from the int field, as in:

totoext({table.field,0,"")

And then link by that field to the varchar field table in the subreport and return data using shared variables, or perhaps even display within the subreport.

Fugly stuff, that.

-k