asked on

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

anand_2000v

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

geeta_m9

ASKER

I cannot use that format, I obtain an "Invalid Number Format Model."

anand_2000v

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

geeta_m9

ASKER

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.

anand_2000v

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

geeta_m9

ASKER

That works! Thank you! However, I need to get it in the format 9,999.99. How can I include that in the statement?

anand_2000v

That should work now.

ASKER CERTIFIED SOLUTION

anand_2000v

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial