A simple question

[julian]

Consider:

SQL> select to_number(3/10) from dual;

TO_NUMBER(3/10)
---------------
.3

Now, you want to see 0.30, not .3. What is the easiest way?

[TimHall]

This works:

select to_char(to_number(3/10), '0.00') from dual;

[TimHall]

Or this:

COLUMN amount FORMAT '0.00';
select to_number(3/10) as amount from dual;

[julian]

Originally posted by TimHall
This works:

select to_char(to_number(3/10), '0.00') from dual;

Thanks Tim but I do neither need the result for SQL*Plus nor I want it to be a varchar2 (other people will sum the values of all columns, so string's noo good).

Something else in mind?

[mrpaulwass]

This is kinda hokey, but how about

select rpad(lpad(to_number(3/10),3,0),4,0) as RESULT from dual

Paul

[jmodic]

Originally posted by julian
I do neither need the result for SQL*Plus nor I want it to be a varchar2 (other people will sum the values of all columns, so string's noo good).

Julian, this is totaly client-side settings problem, not an Oracle isue. Different client environments will display numeric results in different ways. For example, my SQL*Plus displays your result as ".3", while my TOAD displays the same result as "0.3". If your client allows you to set display options fror numeric values, then you can do it (like Tim suggested for SQL*Plus), otherwise you'll have to convert to string and manipulate it from there. Oracle merely returns the correct numeric results, it is clients choice how to represent it.

[julian]

otherwise you'll have to convert to string and manipulate it from there.

I am afraid that's what I have to do. I have made a function which calculates the accured interests of bonds. Since a person might pocess more than one bond at a time, when reporting the bond statistics, we want to show the sum of all accured interests so that numbers such as .78 are displayed as 0.7800

And you are right, this is not an Oracle issue but rather a client enviroment one.