Rick DeBay 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.
>
>Rick DeBay
>Senior Software Developer
>RxStrategies.net
>
>

Hi Rick,

I think the problem shluld be caused by leading or trailing spaces.

1.) Try to use an UDF to trim the values of the char column
2.) Are you sure that the char column are not stored with leading zeroes
(as '001'), in this case the join will not work if the numeric column is
casted to char, you need to cast the char to numeric... you could try
this one

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

I think this could not be the better way, aside from the convertion time
(processing needs), this query could not use the right indices... And if
you have any row with values that could be not converted to numerics you
will get errors.

I think if you create a dummy column and populate it with triggers
(before inser/update) index that column and join using that dummy column
you will have better performance until you have time to change your
database desing...

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br