question

siera_gld avatar image
siera_gld 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 ')
oraclecalculations
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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,
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.