• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1682
  • Last Modified:

How to get negative values to work with To_Number?

I have the following code snippet. It works when the value in the variable TotalDue is 0 or greater. However if TotalDue has a negative value, it returns 0 instead of the correct value. For instance, if TotalDue is 100 and Sum_Memo is 50, it will return 50. However, if TotalDue is -100, then instead of returning -150, it returns 0. TotalDue and SumMemo are string variables. How can I fix it so it will show the correct value. incidentally, the colon in front of the variable just indicates that it an external variable, it does not have any bearing on the final result.

SELECT
To_Char(NVL(To_Number(:TotalDue),0) - NVL(To_Number(:Sum_Memo),0),'999,999.99')
INTO :TotalDuePrint
FROM dual

Open in new window

0
geeta_m9
Asked:
geeta_m9
  • 5
  • 3
1 Solution
 
anand_2000vCommented:
try
SELECT
To_Char(NVL(To_Number(:TotalDue),0) - NVL(To_Number(:Sum_Memo),0),'MI999,999.99')
INTO :TotalDuePrint
FROM dual

Open in new window

0
 
geeta_m9Author Commented:
I cannot use that format, I obtain an "Invalid Number Format Model."
0
 
anand_2000vCommented:
What do you get if you try
SELECT
NVL(To_Number(:TotalDue),0) - NVL(To_Number(:Sum_Memo),0)
INTO :TotalDuePrint
FROM dual

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
geeta_m9Author Commented:
The value in the TotalDue has commas in it, i.e. 2,000 and I believe that is what is affecting the output. How do  I strip out the commas from TotalDue using SQL.
0
 
anand_2000vCommented:
try
SELECT
NVL(To_Number(replace(:TotalDue,',')),0) - NVL(To_Number(:Sum_Memo),0)
INTO :TotalDuePrint
FROM dual
0
 
geeta_m9Author Commented:
That works! Thank you! However, I need to get it in the format 9,999.99. How can I include that in the statement?
0
 
anand_2000vCommented:
That should work now.
0
 
anand_2000vCommented:

SELECT
To_Char(NVL(To_Number(replace(:TotalDue,',')),0) - NVL(To_Number(replace(:Sum_Memo,',')),0),'999,999.99')
INTO :TotalDuePrint
FROM dual

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now