I've tried it and it works well, thanks a lot Oliver.
Amanda
Oliver Elphick wrote:
> 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 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