asked
Oracle Number Formats
I am trying to format numbers coming from an oracle linked server. All Numbers should be 2 decimals. These columns appear to render in SSMS when querying from a linked server. TO_CHAR(WK_SHIPPED_QTY,''9,999,999.99'')WK_SHIPPED_QTY, (trim(RECEIPTS))RECEIPTS, (TO_CHAR(TO_NUMBER(WK_SHIPPED_QTY) - TO_NUMBER(trim(RECEIPTS),''999999.99''),''9,999,999.99'')) As DELTA, TO_CHAR((WEEK_END_DATE),''MM-DD-YYYY'') AS WEEK_END_DATE Now, when this query's results are exported to excel, the only formats that remain correct are the results are 3 digits or more. But i need to force 2 decimals all of the time. I have tried to do cast/converts on the sql server side but get nvarchar/numeric conversion errors
Can you please give example how you are casting/converting? Please note that you cannot use commas(,) while casting/converting to numeric/decimal.
Casting in sql would be something like Select Cast(WK_SHIPPED_QTY AS Decimal(13,2)) AS WK_SHIPPED_QTY From OpenQuery( ORACLESRV, 'Select TO_CHAR(trim(WK_SHIPPED_QTY),''9,999,999.99'')WK_SHIPPED_QTY From ORACLE_TABLE ' If i do this it reveals a nasty error Error converting data type nvarchar to numeric.
This is where you are going wrong. The output in SQL will be X,XXX.XX in a nvarchar format. So when you try to convert this into Decimal, the comma (,) will not let SQL convert it to decimal You can do it in two ways 1.Just to follow you. Otherwise, no need to convert two times SELECT CAST(WK_SHIPPED_QTY AS DECIMAL(13, 2)) AS WK_SHIPPED_QTY FROM OPENQUERY(ORACLESRV, 'Select TO_NUMBER(TO_CHAR(trim(WK_SHIPPED_QTY),''9,999,999.99''))WK_SHIPPED_QTY From ORACLE_TABLE ') 2.Just use to TO_NUMBER and then convert to decimal SELECT CAST(WK_SHIPPED_QTY AS DECIMAL(13, 2)) AS WK_SHIPPED_QTY FROM OPENQUERY(ORACLESRV, 'Select TO_NUMBER(WK_SHIPPED_QTY) WK_SHIPPED_QTY From ORACLE_TABLE ') You can apply the formatting(comma style) in Excel afterwards. Hope this helps.
