×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Plus Number Formatting - to_number vs to_char

SQL Plus Number Formatting - to_number vs to_char

SQL Plus Number Formatting - to_number vs to_char

(OP)
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!

RE: SQL Plus Number Formatting - to_number vs to_char

Quote (Ben)

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:

CODE

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:

CODE

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.

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

RE: SQL Plus Number Formatting - to_number vs to_char

(OP)
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.

RE: SQL Plus Number Formatting - to_number vs to_char

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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close