Sorry, that didn't work. Adding an explicit cast to your query worked.

SELECT *
FROM claimspaidreversed cpr
JOIN claimaudit ca
ON ca.RxClaimNbr = cast(cpr.RXCLAIMNBR as decimal(15,0))
AND ca.RxClaimSeq = cpr.CLMSEQNBR;

Here's the plan for the created view.
PLAN JOIN (V_AUDITEDCLAIMS CPR NATURAL,V_AUDITEDCLAIMS CA INDEX
(CLAIM_FK))

There is an index on claimspaidreversed.RXCLAIMNBR, but I guess it can't
be used here.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, July 08, 2004 7:47 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Joining on columns of different types

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