Subject Joining on columns of different types
Author Rick DeBay
We're porting from MySQL to Firebird 1.5, and I'm having a problem with
a join. There is no error, but no results are returned.
Originally the SQL did an update on joined tables. Since FB doesn't
support this, I'm creating a view with this select statement:

FROM (claimspaidreversed cpr JOIN claimaudit ca
ON (cpr.RXCLAIMNBR=ca.RxClaimNbr AND cpr.CLMSEQNBR=ca.RxClaimSeq))

The problem is that cpr.RXCLAIMNBR is a 15 character VARCHAR padded with
leading zeros, and claimaudit is decimal(15,0). The column
cpr.clmseqnbr is char(3) and ca.rxclaimseq is decimal(3,0), but those
aren't a problem (I tested both parts of the AND, only the rxclaimnbr
failed to return results).

Is there any way around this except for changing claimspaidreversed to
use proper column definitions? I'm slowly moving the database to a
proper design, but I can't afford to change the one table yet.

Rick DeBay
Senior Software Developer