new to Oracle.

function output is number and produces things like this

100
19.95
19.0005
..00625
3.1


I need outputs to be reformated to format out into char(5), no decimal
point rounded to hundreth decial.. looking like this?
10000
01995
01900
00001
00310

On May 7, 3:52 pm, jobs <j...@webdos.com> wrote:
> new to Oracle.
>
> function output is number and produces things like this
>
> 100
> 19.95
> 19.0005
> .00625
> 3.1
>
> I need outputs to be reformated to format out into char(5), no decimal
> point rounded to hundreth decial.. looking like this?
> 10000
> 01995
> 01900
> 00001
> 00310

Try something like:
select to_char(round(19.0005 * 100, 3),'00009') from dual;

TO_CHA
------
01900

though i am not sure I got the rounding right. I do not think your
description of what you want and the example match up. All your
results are the number * 100 except for the .00625 one.

HTH -- Mark D Powell --

On 7 Mai, 22:06, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On May 7, 3:52 pm, jobs <j...@webdos.com> wrote:
> > function output is number and produces things like this
>
> > 100
> > 19.95
> > 19.0005
> > .00625
> > 3.1
>
> > I need outputs to be reformated to format out into char(5), no decimal
> > point rounded to hundreth decial.. looking like this?
> > 10000
> > 01995
> > 01900
> > 00001
> > 00310
>
> Try something like:
> select to_char(round(19.0005 * 100, 3),'00009') from dual;
>
> TO_CHA
> ------
> 01900
>
> though i am not sure I got the rounding right. I do not think your
> description of what you want and the example match up. All your
> results are the number * 100 except for the .00625 one.

Yes, he/she? wants all values multiplied by 100 rounded to integers.

As you've multiplied by 100 already, you would round(value,0) instead
of round(value,3):

select to_char(round(value * 100),'00009') from dual;

And if I am not mistaken to_char does the rounding anyhow if format's
precision doesn't suffice. Hence

select to_char(value * 100,'00009') from dual;

is all you need.

On 8 Mai, 12:01, Thorsten Kettner <thorsten.kett...@web.de> wrote:
> select to_char(round(value * 100),'00009') from dual;
> select to_char(value * 100,'00009') from dual;

Oops that was copy & paste. Format '00009' looks strange. Should be
'00000'.