Received: from dukas.upc.es (dukas.upc.es [147.83.2.62]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3I8QTu87448 for ; Wed, 18 Apr 2001 04:26:30 -0400 (EDT) (envelope-from amanda@labtie.mmt.upc.es) Received: from selene.upc.es (selene.upc.es [147.83.83.65]) by dukas.upc.es (8.10.1/8.10.1) with ESMTP id f3I8LmS08438; Wed, 18 Apr 2001 10:21:48 +0200 (MET DST) Received: from batch.upc.es (root@batch.upc.es [147.83.92.19]) by selene.upc.es (8.8.8/8.8.6) with ESMTP id KAA24749; Wed, 18 Apr 2001 10:26:10 +0200 (MET DST) Received: from labtie.mmt.upc.es (amanda@tester2.upc.es [147.83.92.52]) by batch.upc.es (8.9.3/8.9.3/Debian/GNU) with ESMTP id KAA04139; Wed, 18 Apr 2001 10:26:06 +0200 Message-ID: <3ADD5041.160A562F@labtie.mmt.upc.es> Date: Wed, 18 Apr 2001 10:28:49 +0200 From: Amanda Riera Organization: CTTC X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17 i686) X-Accept-Language: en MIME-Version: 1.0 To: Oliver Elphick , pgsql Subject: Re: Concatenate fields References: <200104111214.f3BCEP205618@linda.lfix.co.uk> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/297 X-Sequence-Number: 2653 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