Subject Re: conversion problem
Author achidan

Thank you for your answer.

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

(Yes, it is a dialect 3 database)

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

It looks like Firebird is choosing a "bad" way to do the
concatenation. And this choice seems to be based on the first field.
When I do the statement:
SELECT DISTINCT '.zZz.' || ParamID || ParamDesc FROM Param

then it's working.
Unfortunately I can't do an explicit cast as the statement is
internally created by IBObjects.