At 07:54 AM 25/01/2009, you wrote:

>> where h.log_tsc > coalesce(z.upd_tsc, z.create_tsc, '1.1.2006')
>>
>> It causes the error: "GDS Exception. 335544321. arithmetic exception,
>> numeric overflow, or string truncation"
>>
>> Please clarify the situation.
>
> I would guess that Firebird is trying to cast all arguments of
>coalesce function to CHAR(8), which is type of '1.1.2008'. And because
>fields are timestamps, they don't fit 8 chars.

I don't think so. The expression coalesce(z.upd_tsc, z.create_tsc, '1.1.2006') doesn't cause an exception (for me) in either v.2.0 or 2.1, even with timestamps in the first two arguments.

Another guess: h.log_tsc might be null for some rows and at runtime the parser barfs on testing it against any of the arguments.

>PS: You can use specification "_timestamp" instead of CAST. I.e.
>coalesce(z.upd_tsc, z.create_tsc, _timestamp '1.1.2006')

Actually, not quite right. The "_XXXXXXX" (known as 'introducer') syntax is for coercing a literal to character set _XXXXXXX. If you try to use it for coercing a data type, you get this exception:


ISC ERROR CODE:335544569

ISC ERROR MESSAGE:
Dynamic SQL Error
SQL error code = -504
CHARACTER SET TIMESTAMP is not defined

For coercing the data type:

coalesce(z.upd_tsc, z.create_tsc, timestamp '1.1.2006')

./heLen