Oracle Conversion TO_NUMBER

Question

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 ')

Answer 1

0

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.

answered Nov 14 '11 at 12:15 PM

WilliamD
25.3k ● 16 ● 18 ● 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 '11 at 12:20 PM siera_gld

(comments are locked)

Oracle Conversion TO_NUMBER

Follow this question

Related Questions