Subject Re: [ib-support] Merging VARCHAR fields
Author Helen Borrie
At 05:39 AM 01-06-02 +0000, you wrote:
>Is there anything wrong with the following? It executes without
>error, but the NOTES field is unchanged afterwards.
>NOTES & DELIVERY_CHARGE are VARCHAR(256) & VARCHAR(40 respectively.
>SET TERM !! ;
>create procedure move_dlvry_chg_to_notes
> DECLARE VARIABLE SuplrNo integer;
> declare variable ss varchar(300);
> for select SUPLRNO, (delivery_charge || ' ' || notes) from
>suppliers into :SuplrNo, :ss
> do
> begin
> update suppliers set notes = :ss where SUPLRNO = :SuplrNo;
> end
>end !!
>SET TERM ; !!

The problem with concatenating two columns is that, if one is null, the
whole result will be null. The usual ways to deal with such a problem are
to either enforce a default empty string on the column at create and update
time (by a trigger); or to perform concatenations via stored procedure
where you can test for null and handle it appropriately.


All for Open and Open for All
Firebird Open SQL Database · ·