x

Oracle Conversion TO_NUMBER

I am more of a sql guy but I need to convert a column in oracle to_number or to_float

I need to do a calc of two columns which appear to be as different formats but I believe the first step in the process is to make sure i can successfully cast both columns as Decimal(13,2) or something.

When i try this cal below i get "ORA-01722: invalid number" errors. but when i comment the cals out, the two columns appear as numbers -

SELECT *  
 FROM OPENQUERY( SLDPRD2_ALT, 'select   
               TO_NUMBER(WK_SHIPPED_QTY)AS WK_SHIPPED_QTY,
          (TRIM(RECEIPTS))AS RECEIPTS
        ,(TO_NUMBER(WK_SHIPPED_QTY) - TRIM(RECEIPTS)) As DELTA
       from Oracle.Table ')
more ▼

asked Nov 14, 2011 at 10:45 AM in Default

siera_gld gravatar image

siera_gld
1k 80 84 85

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest
It looks like you have some data inside one of the columns that will not convert to an integer or decimal value. I don't know about oracle enough to say what you could do there, other than to get both columns as a standard select and then try the conversion on each column separately. If only one fails, that will narrow down the search for the problem. SQL Server 2012 has a parse option that attempts a parse and gives back true or false. You can then find the data that is bad from there.
more ▼

answered Nov 14, 2011 at 12:15 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

no - i got it - had to to this

TO_NUMBER(TRIM(RECEIPTS),'999999.99')AS RECEIPTS,
(TO_NUMBER(WK_SHIPPED_QTY) - TO_NUMBER(trim(RECEIPTS),'999999.99')) As DELTA

Thanks,

Nov 14, 2011 at 12:20 PM siera_gld
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x379
x25

asked: Nov 14, 2011 at 10:45 AM

Seen: 2027 times

Last Updated: Nov 14, 2011 at 10:45 AM