At 10:33 AM 9/07/2004 -0400, you wrote:

>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.

Correct, it can't. A cast is an expression.

Out of curiosity, is there any special reason you have this number declared
as decimal(15,0), rather than BigInt?

/hb

>-----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
>
>
>
>
>Yahoo! Groups Links
>
>
>
>