From mark@cool-tools.co.uk Fri, 01 Jun 2001 04:07:05 -0700 From: "Mark Leith" Date: Fri, 01 Jun 2001 04:07:05 -0700 Subject: RE: select question Message-ID: MIME-Version: 1.0 Content-Type: text/plain Title: RE: select question My thought entirely - or to enhance slightly more, as you have quite a large number pre-decimal place:   to_char(total, '$9,999.99')   or   to_char(total, 'FM$9,999.99') as Jacques suggested..   This will give you a slightly better output format, with a comma every 3 numbers.   HTH   Mark -----Original Message-----From: root@fatcity.com [mailto:root@fatcity.com]On Behalf Of Jacques KilchoerSent: Thursday, May 31, 2001 10:27To: Multiple recipients of list ORACLE-LSubject: RE: select question > -----Original Message----- > From: Ron Rogers [mailto:RROGERS@galottery.org] > > If you know the size of the number (in position count not > value) you can use the RPAD(sum(field),count,0) to populate > the right end of the return value to zero.  > ex. the sum value is = 2.5 and you want 2.50 > rpad(sum(fd1,4,'0') return 2.50 > > BUT if the return value = 25.5 and you want 25.50 and have > used the 2.50 rpad(sum(fd1,4,'0')  the return 25.5  will be > returned. You could always use > 2.50 rpad(sum(fd1,6,'0') and then drop the unneeded zeroes. Am I missing something? Why not use to_char (number_field, '99.99')? or to_char (number_field, 'FM99.99') to eliminate leading and trailing blanks in the formatted number