Display Numbers 2

[Andrzejek]

I have a field in the table declared as[tt] NUMBER(10,2)[/tt]
In some controls (in VB6) when I display the outcome from my Select statement, I get:
[tt]
.50
1.00
10.00
15.30[/tt]

I solved the problem of displaying zeros by:

Select SomeField * 1 as MyField From ...

And I get:
[tt]
.5
1
10
15.3[/tt]

(I'm sure there is a better way to do it...?)

But now, what do I need to do (in my Select statement) to get [tt]0.5[/tt] (zero point five) - instead of [tt].5[/tt] (point five)?

Have fun.

---- Andy

 

[SantaMufasa]

Andy,

Here are some values (that are not decimal aligined):

select last_name,salary from s_emp;

LAST_NAME                     SALARY
------------------------- ----------
Velasquez                       2750
Ngao                            1595
Nagayama                        1540
Quick-To-See                    1595
Ropeburn                        1705
Urguhart                        1320
Menchu                          1375
Biri                            1210
Catchpole                       1430
Havel                         1437.7
Magee                           1540
Giljum                          1639
Sedeghi                       1666.5
Nguyen                        1677.5
Dumas                           1595
Maduro                          1540
Smith                           1034
Nozaki                          1320
Patel                          874.5
Newman                           825
Markarian                        935
Chang                            .04
Patel                          874.5
Dancs                            946
Schwartz                        1210

25 rows selected.

You can use Oracle's TO_CHAR function on a number expression to give the numbers whatever appearance you would like:

select last_name,to_char(salary,'99,990.99') salary from s_emp;

LAST_NAME                 SALARY
------------------------- ----------
Velasquez                   2,750.00
Ngao                        1,595.00
Nagayama                    1,540.00
Quick-To-See                1,595.00
Ropeburn                    1,705.00
Urguhart                    1,320.00
Menchu                      1,375.00
Biri                        1,210.00
Catchpole                   1,430.00
Havel                       1,437.70
Magee                       1,540.00
Giljum                      1,639.00
Sedeghi                     1,666.50
Nguyen                      1,677.50
Dumas                       1,595.00
Maduro                      1,540.00
Smith                       1,034.00
Nozaki                      1,320.00
Patel                         874.50
Newman                        825.00
Markarian                     935.00
Chang                           0.04
Patel                         874.50
Dancs                         946.00
Schwartz                    1,210.00

25 rows selected.

Notice that the "0" in the format mask determines where the "leading-zero suppression" stops.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SamBones]

column SomeField format 9,990.00

 

[Andrzejek]

Thank you both,
I was looking at TO_CHAR function, but I guess I was not going far enough with it.

Have fun.

---- Andy