Home » SQL & PL/SQL » SQL & PL/SQL » to_num
to_num [message #293166] Fri, 11 January 2008 01:12 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

Cuurently we r using SUM(TO_NUMBER(P.paymentamount, '9,999,999,999,999.99')) - paymentamount is a varchar2(30)

Now I wanted to use this SUM(TO_NUMBER(P.paymentamount, ' 9,999,999,999,999,999,999.99')) , my question is does this function

To_number allows us to increase format to this length . Let me know abt it

thank u.

[Updated on: Fri, 11 January 2008 02:06] by Moderator

Report message to a moderator

Re: to_num [message #293168 is a reply to message #293166] Fri, 11 January 2008 01:18 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

TO_NUMBER . It is not incresing the length.

Whatever the reason it is not good practice to make the payment amount field as varchar2 field .

Thumbs Up
Rajuvan.

[Updated on: Fri, 11 January 2008 01:25]

Report message to a moderator

Re: to_num [message #293169 is a reply to message #293166] Fri, 11 January 2008 01:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In my opinion, numeric values (such as "paymentamount" is) should be stored in the NUMBER datatype columns. I don't know what made you put it into a VARCHAR2 column and, even worse, format it ... that's (excuse my French) stupid.

Always store numbers as numbers. It is easy to format them for reporting purposes, and all arithmetic operations are easily done. And see what you have now ... an utter mess. If possible, I'd suggest you to make it right and change column's datatype.

By the way, what is the expected result of this SUM operation you'd like to do? A formatted number? If so, apply the TO_CHAR function to the numeric result.
Re: to_num [message #293171 is a reply to message #293166] Fri, 11 January 2008 01:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I have seen some application makes use of some other logic.

Making Amount field as Number(x).
And they store the values in Multiplication facture.say 100.

Ie, USD 15.35 will be stored as 1535
USD 25 will be stored as 1500

So it is easy to make mathematical calculation more clearly irrespective of currency.

I feel this also good logic.

Thumbs Up
Rajuvan
Re: to_num [message #293174 is a reply to message #293168] Fri, 11 January 2008 01:41 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

So Please give me solution for this..

Thank you.
Re: to_num [message #293177 is a reply to message #293166] Fri, 11 January 2008 01:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

You didn't specify the exact problem yet .

Thumbs Up
Rajuvan.
Re: to_num [message #293178 is a reply to message #293174] Fri, 11 January 2008 01:49 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Solution for what?

Post a sample test case for us: CREATE TABLE and INSERT INTO several records. Write a query you use now and how it differs from a result you'd like to have.
Previous Topic: About Oracle data dictionary view
Next Topic: Logical operations
Goto Forum:
  


Current Time: Fri Dec 09 21:41:28 CST 2016

Total time taken to generate the page: 0.09344 seconds