Formatting numeric value of calculated column in View

Question

Post reply

Formatting numeric value of calculated column in View

sg2000-429020

Ten Centuries

Points: 1046
More actions
January 4, 2008 at 11:15 am

#181909

I created a calculated column in a View. The calculated column contains a numeric value calculated by dividing two values from other columns in the View. However, the result in the calculated column contains too many decimals.
How can I round up the value of the calculated column to 3 decimal places (with no trailing zero's) while keeping it a numeric value? The ROUND function still leaves trailing zero's.
Thanks in advance,
sg2000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

if you care to use SQL's built in rounding, you can simply use the convert function:

select convert(decimal(10,3),108451.4587) results in 108451.459

so simply format the column in your view as convert(decimal(10,3),columnmname)

if you have some special way you need to "round up", you'd need to do that, but still return a decimal with 3 places.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sg2000-429020 Ten Centuries Points: 1046 More actions · Answer 2

Lowell:

Thanks for the quick response. However, when I tried the method you suggested on the calculated column Ratio (see below) and execute the SQL, I got SQL Execution Error window that says: "Arithmetic overflow error converting numeric to data type numeric". Any idea why?

By the way, my SQL code is as follows:

CONVERT(decimal(3, 3),

dbo.TabA.ColA / dbo.TabA.ColB * 100) AS Ratio,

Thanks,

sg2000

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

that usually means you don't have enough slots in from of the decimal point to cover everything. Try casting to decimal(10,3)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 4

if the NUMERIC_ROUNDABORT option is ON for the database/session, any operation that results in loss of precision will cause an overflow error. all of the following statements will fail because the underlying datatype is numeric( ?, 4):

select cast(108451.4587 as decimal(12,3))

select convert(decimal(12,3),108451.4587)

select cast(round(108451.4587,3) as decimal(12,3))

the value simply won't fit in a numeric(12,3) without a loss of precision. round()ing won't help since it always returns the same datatype it was fed.

you have 3 alternatives:

1) SET NUMERIC_ROUNDABORT OFF for the db/session and use any of the earlier suggestions

2) convert the value to a float prior to rounding and then cast that into the appropriate numeric:

select cast( cast(round(108451.4587,3) as float) as decimal(12,3))

3) do nothing (my recommendation). the app/process that is using the view should be responsible for formatting the numeric data to suit its display preferences.

sg2000-429020 Ten Centuries Points: 1046 More actions · Answer 5

Matt:

Yes, you are right. I changed it to Convert (decimal (5,3), xxxx....) and it works. Thanks!

sg2000

sg2000-429020 Ten Centuries Points: 1046 More actions · Answer 6

Antonio:

Thanks very much for the alternatives. As posted before, I use Convert (decimal (5,3), xxx...) instead of Convert (decimal (3,3), xxx...) and it does work. This is the best option, I think. Thanks again.

sg2000