Joining on diff data types (should be a simple 1!!)

[unifex]

I should know this but can't seem to get the thing to work.
Check out this code:

Select *
from x
inner join y
on x.ACCTNUM = y.EXTPRDID

So ACCTNUM is Decimcal(12,0) and EXTPRDID is Char(20)... this might be Varchar(20) as there seems to be some difference between out data dictionaries!!

This don't work (obviously)

I put in a cast to get the join thus:

on cast(x.ACCTNUM as char(20)) = y.EXTPRDID

Efficiency issues aside this should work as far as i can tell but nope not matching on any records even though i can manually match by putting in literals.

What on earth is wrong!?

I tried casting BOTH as char(20) - nope.

I can't cast EXTPRDID as integer or decimal as it has characters in some cases.

I have only read access to table 'y' but table 'x' i can hack to bits anyway i feel like (if that helps).

Any advice would be apprecaited.

Thanks

 

[BillDHS]

I'll give you a hint but you will have to test. If y.EXTPRDID is VARCHAR I don't think they will match. Try casting that as CHAR(20) or making cast(x.ACCTNUM as char(20)) a VARCHAR. I think the spaces are preventing matches.

 

[dnoeth]

What format is used for ACCTNUM?
The default will include a dot, e.g. '123456789012.'

Is the numeric string in EXTPRDID left or right aligned?
' 123456789012'
'123456789012 '

Try
trim(x.ACCTNUM (format 'z(12)') = trim(y.EXTPRDID)

Dieter