SQL Plus Number Formatting - to_number vs to_char

[benamorton]

Hi all,
I am working with a VIEW in SQL plus (9i or 11g) and for some reason when it is returning results (dollar amount column), it is leaving off the decimal places, essentially just rounding down.

I know I can do a to_char on this (select to_char(amt,'999,999,999.99') from transactions) but I want the output to be a number, so this is not an option for me.

I have tried this, but it is not working as expected: select to_number(amt,'999,999.99') from trans
TO_NUMBER(AMT,'999,999.99')
---------------------------
-16
16
-13
13

Any thoughts would be greatly appreciated, thanks!

 

[SantaMufasa]

I am working with a VIEW in SQL plus

To be precise, Ben, you are not working with a VIEW in SQL*Plus, you are working with a view in SQL.

SQL and SQL*Plus are two entirely different languages, with entirely different reasons for living, with commands and syntax that bear no resemblance to one another. (We access and manipulate Oracle databases with SQL commands; we can format results with SQL*Plus commands.)

You can use the SQL function, TO_CHAR, to reformat numeric values into results that appear with a consistent character format. You can use the SQL function, TO_NUMBER, to "recast" characters (which must evaluate to numbers) into arithmetically operable values. The second case, (TO_NUMBER) will not yield the results you want; the first case (TO_CHAR) can produce the result you want, but you have ruled out its use.

I have re-produced your TRANSACTION table that you posted (consisting of four rows of integers), plus, I've added a row (consisting of a real number) to illustrate that your assertion that Oracle "is leaving off the decimal places, essentially just rounding down" is not accurate:

select * from transaction;

       AMT
----------
       -16
        16
       -13
        13
  -123.456

5 rows selected.

In the code below, I re-use your SQL statement from above, but prior to the execution, I added a SQL*Plus statement to format the output to appear with two decimal places:

col amt format 999,999,999.99
select * from transaction;
            AMT
---------------
         -16.00
          16.00
         -13.00
          13.00
        -123.46

5 rows selected.

Please post here if you have additional questions about the above code.

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

 

[benamorton]

Thanks Dave, this is great. I will look at how I can get this formatting including in the backend of the application prior to the view execution.

 

[SantaMufasa]

Ben,

If the purpose of this exercise is to create a VIEW, then you don't want to "futz" with the formatting of any numbers. You want to leave any numbers (in the view) in their raw, numeric forms, then do any "decimal-and-comma" formatting on the backend display of the view contents at query time.

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