Received: from temp.joelburton.com (unknown [209.190.238.101]) by postgresql.org (Postfix) with ESMTP id 151C44758EF for ; Fri, 26 Apr 2002 17:32:24 -0400 (EDT) Received: from joeltf0d13cyyp (unknown [209.190.238.100]) by temp.joelburton.com (Postfix) with SMTP id D149B2BED2; Fri, 26 Apr 2002 17:35:12 -0400 (EDT) From: "Joel Burton" To: "Robert Wynter" , Subject: Re: union query format Date: Fri, 26 Apr 2002 17:32:14 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal In-Reply-To: <44586AAA5ECBD511A6520004AC2589A652D81F@rssesnexg.rogers.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200204/338 X-Sequence-Number: 7602 > Hi I'm trying to combine 3 query's in a union query as follows: > > rowdate nco nca nch aht asa svl > %abandon > > With Query 1; > > 04/01/02 1000 500 500 450 23 .23 .50 > 04/02/02 2000 500 1500 450 23 .23 .50 > > plus Query 2; > > wtd 3000 1000 2000 450 23 .23 .50 > > and Query 3; > > mtd 3000 1000 2000 450 23 .23 .50 > > > I have 2 problems once the Union Query has run. > 1. because Query 2 & 3 are text, it's forcing Query 1 into text and the > date order is all messed up. > 2. I need to formate the fileds for svl & % abandon as a percentage. Something like: SELECT rowdate AS sort, rowdate::text, nco, ..., asa * 100 || '%' FROM query1 UNION SELECT 'infinity'::timestamp, rowdate, ... FROM query2 UNION SELECT 'infinity'::timestamp, rowdate, ... FROM query2 ORDER BY 1 This will let you sort by the first column as dates (keeping wtd and mtd at the end). To show a number as percentage, x * 100 appended with '%' works fine. See to_char() if you need more control.