LEFT JOIN on different data types CR10

[KTTapee]

I have 2 DB2 tables that I need to join on fields that are different data types, one is a string, the other a number. When I use a DB2 query tool, I can use the funtion "DECIMAL" to make the join work. In Crystal, I have tried many functions to get the join to work, without success. Here is the join with the DB2 syntax. Any suggestions are greatly appreciated.

SELECT EQ01DM04.EQUIP_ID,EQ01DM04.CONS_NAME
,CV02.CV_LOC_ADDR_LINE_1,CV02.CV_LOC_ADDR_LINE_2
,CV02.CV_LOC_CITY,CV02.CV_LOC_STATE
,CV02.CV_LOC_CNTRY_CODE FROM
P.EQ01D04_EQUIP EQ01DM04
LEFT JOIN P.CV01_CORP_DESC CV01 ON CV01.CV_NBR
= DECIMAL(EQ01DM04.CONS_CVIF_NBR)
LEFT JOIN P.CV02_CV_LOC CV02
ON CV02.CV01CV02_KEY = CV01.PRIMARY_KEY
AND CV02.CV_LOC_CODE = EQ01DM04.CONS_CVIF_LOC

 

[synapsevampire]

Select Add Command under the DB2 connection and paste your query in there.

-k

 

[KTTapee]

I have tried both an ODBC as well as an ADO/OLE DB connection to DB2 and then running the query as a command. Crystal does not recognize the "DECIMAL" function. I have tried using ToNumber, Cstr etc to convert either of the fields to the other type, but nothing works.

Any other suggestion would be great. Thanks!

Kirsten