help joining files with different key data types 1

[focusdev]

I'm trying to join two tables together where the key from the host table is a two digit number stored in packed format, and the secondary table is a four digit number stored in a char(10) field.

For example, the value from the host would be 23 and the value that it would match to in the guest would be '1023', so 1000 needs to be added to the value in the host, converted to char(10) and matched on the guest.

I've tried to use cast, but recieved an error that char could not be used.

I was wondering if anyone might have some other ideas on how this might be possible.

 

[Mercury2]

WHERE TRIM(CHAR(GUEST.FIELD)) = TRIM(CHAR(HOST.FIELD + 1000))
should do the trick ?

 

[Mercury2]

oops ! I mean to say
WHERE TRIM(GUEST.FIELD) = TRIM(CHAR(HOST.FIELD + 1000))

 

[focusdev]

when using trim and char, I still get a -420

22018 Character in CAST argument not valid

 

[MarcLodge]

focusdev,
try something like:

WHERE SUBSTR(DIGITS(HOST.COLUMN + 1000),1,4) = GUEST.COLUMN

You'll have to adjust the value '1' in the SUBSTR part according to the actual size of your decimal column.

Marc

 

[focusdev]

Thanks MrcLogde - that worked perfectly.