Re: Concatenate fields

Amanda Riera wrote:
>I would like concatenate some fields to have all information in just
>one field. I'm doing this below:
>
>CREATE TABLE bill_2col AS
>SELECT bill.bill_id,
> (trim(text(bill.bill_number)) || ' | ' ||
> trim(text(provider.company)) || ' | ' ||
> trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' ||
> trim(to_char(bill.amount,'9999999.99')) || ' pts') AS billdesc
>FROM bill, provider
>WHERE bill.provider_id = provider.provider_id
>ORDER BY bill.bill_id;
>
>When it finds some empty field, it makes all the new field empty, no
>matters
>if the other are empty or not.

In this case, empty means NULL. Any concatenation involving NULL returns
NULL; this is according to the standard.

Use COALESCE(field,'') to return an empty string if field is NULL, so
that no NULLs go into the concatenation.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Is any one of you in trouble? He should pray. Is
anyone happy? Let him sing songs of praise. Is any one
of you sick? He should call the elders of the church
to pray over him...The prayer of a righteous man is
powerful and effective." James 5:13,14,16