Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Question - Number formatting for report
what tool are you using to show the output. In sqlplus, you can use the
column directive to control the format of the output. Other query tools
have other options.
danmer_at_my-deja.com wrote in message <865f57$shc$1_at_nnrp1.deja.com>...
>In article <864tjt$p$1_at_nntp.itservices.ubc.ca>,
> "George Dimopoulos" <george_at_cheos.ubc.ca> wrote:
>> Using Oracle 8
>>
>> Have a field of number(12,4) which I would like to print out on a
>report
>> based on following format criteria
>>
>> If the value is less then 1, then print a zero infront of decimal
>>
>> ie .12 should print as 0.12
>>
>> If the value is a whole integer (no decimal value) then don't print
>decimal
>> or zero
>>
>> ie 1 should print as 1
>>
>> If the value is greater then 1 and has decimal values then print as is
>>
>> ie 23.333 should print as 23.333
>>
>> any ideas on how to do this using to_char(...., {format})?
>>
>> thx in advance.
>>
>> Please email me and I will post results.
>> --
>> George Dimopoulos
>> george_at_cheos.ubc.ca
>>
>>
>George,
>
>I dont know how to do what you ask in a to_char format but perhaps you
>can accomplish your goal using a decode in the SELECT statement the
>report is built upon:
>
>SELECT decode( (trunc(number_column)-number_column), 0,
> to_char(number_column, '999999999'),
> to_char(number_column, '999999990.99')
> ) display_number
> FROM ...
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Jan 20 2000 - 19:02:25 CST