At 11:26 AM 06-04-02 +0000, you wrote:

>Hi
>
>I'm using firebird 1.0. I have some select statements which returns
>the bug
>
>arithmetic exception, numeric overflow, or string truncation
>Cannot transliterate character between character sets
>
>My database and connection is with Character set ISO8856_1.
>
>My table has the following fields:
>ParamID: Numeric(18, 0)
>ParamName: VarChar(10) ISO8856_1
>ParamDesc: VarChar(40) ISO8856_1
>
>I get the error with the following statement:
>SELECT DISTINCT ParamID || ParamDesc FROM Param
>
>I don't get the error with the following statements:
>SELECT ParamID || ParamDesc FROM Param (without Distinct)
>SELECT DISTINCT ParamID || ParamName FROM Param (with VarChar(10) than
>VarChar(40))
>SELECT DISTINCT ParamDesc || ParamID FROM Param (different field order)
>SELECT DISTINCT ParamName || ParamDesc FROM Param (only varchar fields)
>
>For me it seems to be a bug or is it "by design"? Has someone some
>background information about the internal reason for this behavior?
>
>The bad side about this bug is that IBObjects internally uses such
>kind of select statements. So I cannot avoid is so easily.

Until/unless Claudio or Dave S. has a better suggestion, I suspect that
your problem comes in the casting of the numeric(18,0) to a string and you
might be getting a not entirely appropriate error message back. If you are
using a dialect 3 database (you didn't say) then you are expected to cast
the numeric(18,0) explicitly as a character type in order to use it in a
concatenation. Some of the statements appear to be allowing the implicit
cast but it's not recommended in dialect 3... select distinct might be
especially sensitive because you are asking it to perform its internal
sorting on this "hairy" concatenation.

regards,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________