Lesnet


How do I set Precision and Scale in a calulated column

I'm trying to limit the decimal points returned in a calculated column but can't find where to set the scale. What am I missing please

Thanks,

Scott




Re: Precision and Scale in a calulated column

Andrea Montanari


hi Scott,

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE #t (
	d decimal(18,4) DEFAULT 1234.123,
	D2 AS CONVERT(decimal(8,2), d)
	);
GO
INSERT INTO #t DEFAULT VALUES;
SELECT * FROM #t;
GO
DROP TABLE #t;
--<---------
d      D2
------------ --------
1234.1230  1234.12

regards







Re: Precision and Scale in a calulated column

Arnie Rowland

Either using a CREATE TABLE or ALTER TABLE statement, and add the precision,scale to the computed column declaration.

Here is an example:


CREATE TABLE #MyTable
( RowID int IDENTITY,
Cost decimal(6,2),
Quantity int,
Total AS cast(( Cost * Quantity ) AS decimal(8,4))
)

INSERT INTO #MyTable VALUES ( 5, 10 )
INSERT INTO #MyTable VALUES ( 2.5, 5 )
INSERT INTO #MyTable VALUES ( 4.55, 5 )

SELECT * FROM #MyTable

DROP TABLE #MyTable







Re: Precision and Scale in a calulated column

Lesnet

Thank you for helping.

I'm using the Management Studio. Is there a way to accomplish this in the formula line

Thanks again.

Scott





Re: Precision and Scale in a calulated column

Lesnet

Thanks for helping.

I tried the following:

***********************************

Use SIR
ALTER TABLE dbo.Table_1
ALTER COLUMN Results
nPRECISION(6,2)

Go

************************************

Recieved the following message:

************************************

Msg 4928, Level 16, State 1, Line 2
Cannot alter column 'Results' because it is 'COMPUTED'.

What else might I try





Re: Precision and Scale in a calulated column

Arnie Rowland

You need to first DROP the computed column, then ADD it back, and you MUST include the computation formula.

Do something like this:

USE SIR;

ALTER TABLE dbo.Table_1
DROP COLUMN Results;

ALTER TABLE dbo.Table_1
ADD COLUMN Results cast( ( [put formula here] ) AS decimal(6,2)));