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

# 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
0
geeta_m9
• 5
• 3
1 Solution

Commented:
try
``````SELECT
To_Char(NVL(To_Number(:TotalDue),0) - NVL(To_Number(:Sum_Memo),0),'MI999,999.99')
INTO :TotalDuePrint
FROM dual
``````
0

Author Commented:
I cannot use that format, I obtain an "Invalid Number Format Model."
0

Commented:
What do you get if you try
``````SELECT
NVL(To_Number(:TotalDue),0) - NVL(To_Number(:Sum_Memo),0)
INTO :TotalDuePrint
FROM dual
``````
0

Author 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

Commented:
try
SELECT
NVL(To_Number(replace(:TotalDue,',')),0) - NVL(To_Number(:Sum_Memo),0)
INTO :TotalDuePrint
FROM dual
0

Author 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

Commented:
That should work now.
0

Commented:

``````SELECT
To_Char(NVL(To_Number(replace(:TotalDue,',')),0) - NVL(To_Number(replace(:Sum_Memo,',')),0),'999,999.99')
INTO :TotalDuePrint
FROM dual
``````
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.