The || function is explicitly for string concatenation, as stated in
embedsql.pdf:

"The string operator, ||, also referred to as a concatenation
operator, enables a single character string to be built from two or
more character strings. Character strings can be constants or values
retrieved from a column."

Why it lets this through:

> SELECT DISTINCT '.zZz.' || ParamID || ParamDesc FROM Param

but not

> SELECT DISTINCT ParamID || ParamDesc FROM Param

(at a guess) is because the parser assumes all types of subsequent
items must match the first and tries to cast accordingly.

Ann, is there a reason why the logic for internal concatenation can't
be more intelligent, like:

* try casting all params as numbers; if no errors then do numeric
addition, otherwise;

* cast all as strings and concat.

??

Regards,
Andrew Ferguson

--- In ib-support@y..., "achidan" <ac.hi@s...> wrote:
> Helen,
>
> 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
> 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.
>
> 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.
>
> Regards
> Daniel