| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Question - Number formatting for report
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
 |
 |