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

geeta_m9Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
anand_2000vConnect With a Mentor Commented:

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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.