At 12:37 PM 8/07/2004 -0400, you wrote:

>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:
>
>SELECT *
>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.

Arno may have something to say about this when he comes online but my guess
is that the implicit cast is happening the wrong way around because you
have the character value on the left. This is causing the engine to cast
RxClaimNbr to varchar and, of course, there can't be a match because of
those leading zeros.

What you want is for the varchar to be cast as integer. So try reversing
the situation and force the implicit cast to go the other way:

SELECT *
FROM claimspaidreversed cpr
JOIN claimaudit ca
ON ca.RxClaimNbr /* integer */ = cpr.RXCLAIMNBR /* varchar */
AND ca.RxClaimSeq /* smallint */ = cpr.CLMSEQNBR /* char */

On this argument, you see I also reversed the comparison that you said
worked. It ought to work the way you had it (usually) since equality
comparisons disregard trailing blanks. I just think it's good to follow a
consistent rule with comparison logic, ESPECIALLY when implicit casting is
going on.

/heLen