Stephen Boyd wrote:

> I am playing around with COALESCE in Firebird 1.5.3. I have a table
> with this column:
>
> LD_SHIP_ADDR1 VARCHAR(32) = 'X'
>
> If I execute the following statement in ISQL I get the result that I
> expect:
>
> SELECT FIRST (1) COALESCE(LD_SHIP_ADDR1, 'No address') FROM LOADS;
>
> If I put the same statement into a stored procedure like this:
>
> CREATE PROCEDURE TEST() RETURNS (RETVAL VARCHAR(99)) AS
> BEGIN
> SELECT FIRST (1) COALESCE(LD_SHIP_ADDR1, 'No address') FROM LOADS
> INTO :RETVAL;
> SUSPEND;
> END;
>
> then execute the stored procedure like this:
>
> SELECT * FROM TEST
>
> I get an error:
>
> Conversion error from string "X".
>
> Is this a bug or am I just doing something stupid

I think it should work too :-/

I would try two things to "fix" it:
1.) Use Substring to force the size
2.) Use Cast


CREATE PROCEDURE TEST() RETURNS (RETVAL VARCHAR(99)) AS
BEGIN
SELECT FIRST (1) Substring(COALESCE(LD_SHIP_ADDR1, 'No address') from 1 for 99) FROM LOADS
INTO :RETVAL;
SUSPEND;
END;


CREATE PROCEDURE TEST() RETURNS (RETVAL VARCHAR(99)) AS
BEGIN
SELECT FIRST (1) CastSubstring(COALESCE(LD_SHIP_ADDR1, 'No address') from 1 for 99) as varchar(99)) FROM LOADS
INTO :RETVAL;
SUSPEND;
END;


Really don't know why it is complaining, does the LD_Ship_Addr1 has a
different charset (or something like it) ?

see you !

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