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