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.Elphick@lfix.co.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
singsongs of praise. Is any one     of you sick? He should call the elders of the church     to pray over him...The
prayerof a righteous man is     powerful and effective."         James 5:13,14,16