x

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
more ▼

asked Nov 15 '11 at 11:30 AM in Default

siera_gld gravatar image

siera_gld
1k 74 80 83

Can you please give example how you are casting/converting? Please note that you cannot use commas(,) while casting/converting to numeric/decimal.
Nov 15 '11 at 09:40 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

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.

more ▼

answered Nov 16 '11 at 09:02 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 16 '11 at 09:26 AM

siera_gld gravatar image

siera_gld
1k 74 80 83

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x376
x109
x29
x26

asked: Nov 15 '11 at 11:30 AM

Seen: 2264 times

Last Updated: Nov 15 '11 at 11:30 AM