Subject RE: [firebird-support] Joining on columns of different types
Author Rick DeBay
The data as defined by the vendor who's sending us the claims is that
it's 15 digits padded with zero. I'm slowly enforcing data constraints
on our database.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Friday, July 09, 2004 10:50 AM
Subject: RE: [firebird-support] Joining on columns of different types

At 10:33 AM 9/07/2004 -0400, you wrote:
>Sorry, that didn't work. Adding an explicit cast to your query worked.
>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.
>There is an index on claimspaidreversed.RXCLAIMNBR, but I guess it
>be used here.

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

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


>-----Original Message-----
>From: Helen Borrie [mailto:helebor@...]
>Sent: Thursday, July 08, 2004 7:47 PM
>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
> >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
> >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
> >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
>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
>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
>the situation and force the implicit cast to go the other way:
>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
>consistent rule with comparison logic, ESPECIALLY when implicit casting
>going on.
>Yahoo! Groups Links

Yahoo! Groups Links