Oracle Number Formats

Question

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

Answer 1

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.

Answer 2

0

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.

answered Nov 16 '11 at 09:26 AM

siera_gld
936 ● 52 ● 70 ● 73

(comments are locked)

Oracle Number Formats

Follow this question

Related Questions