Solved

# How to get negative values to work with To_Number?

Posted on 2007-12-04
1,293 Views
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
Question by:geeta_m9
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

LVL 13

Expert Comment

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

Author Comment

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

LVL 13

Expert Comment

ID: 20406001
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 Comment

ID: 20406057
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

LVL 13

Expert Comment

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

Author Comment

ID: 20406150
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

LVL 13

Expert Comment

ID: 20406163
That should work now.
0

LVL 13

Accepted Solution

anand_2000v earned 500 total points
ID: 20406175

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.