Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help joining files with different key data types 1

Status
Not open for further replies.

focusdev

Programmer
May 9, 2007
18
US
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.
 
WHERE TRIM(CHAR(GUEST.FIELD)) = TRIM(CHAR(HOST.FIELD + 1000))
should do the trick ?
 
oops ! I mean to say
WHERE TRIM(GUEST.FIELD) = TRIM(CHAR(HOST.FIELD + 1000))
 
when using trim and char, I still get a -420

22018 Character in CAST argument not valid
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top