sql permissions: scale

Showing posts with label scale. Show all posts

Friday, March 9, 2012

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 / 464

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:
How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:
I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800/ 464

SELECTCONVERT(DECIMAL(10,2), 6800/ 464)

They give me 14.66.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:
How are you dong this calculation in SSIS and how are you visualising the result?
I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

DarrenSQLIS wrote:
I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 /

464

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:
How are you dong this calculation in SSIS and how are you visualising the result?
I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

DarrenSQLIS wrote:
I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

Wednesday, March 7, 2012

Decimal field is rounding up my numbers

Hi,

I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.

Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.

how are you passing the values from the asp.net page? whats the datatype? can you post the asp.net code? also if you manually insert the values from query analyzer does it insert the right values?

|||

Hi,

I entered 2.5 using QueryAnalyzer and SQL Server stored it as 2. So the issue is with the way I defined this Decimal field in SQL Server. The problem is I don't see where I'm making a mistake. If I set the precision to 3 and scale to 1, I should be able to have values ranging from 0.5 to 10.0 in there, should I?

|||your scale is right. try increasing your precision to 5. also check if you are working with the right column.|||Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.|||

SamU wrote:
Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.

I have added a testDecimal column to my Test table, and set it up as a decimal field with a precision of 3 and a scale of 1, and I am not seeing this odd behavior. It is storing the data exactly as I supply it (3.5, .5, 10.0, etc.) Please explain how exactly you are "doing this in Query Analyzer" as QA does not give you the facility to directly update the data; you need to execute a query to do this. I used queries like this:

UPDATE test SET testDecimal = 3.5 WHERE ID = 4
SELECT testDecimal FROM test WHERE testDecimal IS NOT NULL

For reference, when I first added the testDecimal column through Enterprise Manager, the default length was 9, the precision was 18, and the scale was 0. I changed the precision to 3 and the scale to 1, and the length automatically changed to 5.|||

We may be narrowing this down. I'm actually inserting new values into a table and I'm doing this through a stored procedure. The parameter that inserts the value is defined as decimal. I'm including the code down below. Do I need any additional values that further define the parameter's precision and scale in the stored procedure? Looks like it's the stored procedure that's rounding the number up, not the table. Can anyone see an issue w/ this stored procedure?

Here's the Stored Procedure code. The parameter that inserts value into this field is @.JobLength.

ALTER PROCEDURE dbo.spTalentReleaseNew
(
@.EmployeeID smallint,
@.JobName varchar(200),
@.JobDate smalldatetime,
@.DealID int,
@.JobLength decimal,
@.TalentAgencyName varchar(200) = null,
@.TalentID int,
@.TalentRate smallmoney,
@.LocationRate smallmoney,
@.MakeUpRate smallmoney,
@.FoodStylistRate smallmoney,
@.LastUpdateTimeStamp datetime
)
AS
/* ObjectID = 221; This stored procedure creates a new Talent Release. */
INSERT INTO tblTalentRelease
(EmployeeID, JobName, JobDate, DealID,JobLength, TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate, FoodStylistRate,
LastUpdateTimeStamp, LastUpdatedBy)
VALUES (@.EmployeeID, @.JobName, @.JobDate, @.DealID,@.JobLength, @.TalentAgencyName, @.TalentID, @.TalentRate, @.LocationRate, @.MakeUpRate,
@.FoodStylistRate, @.LastUpdateTimeStamp, @.EmployeeID)

|||That's the problem. If you just declare the @.JobLength as decimal, it is created with a scale equal to zero as the default (and precision of 18). Set it like this instead:

@.JobLength decimal(3, 1)

and you should be fine.

Don|||THanks Don.

decimal division precision

in my understanding of fixed numeric types and datatype precision rules,
dividing two decimals with identical precision/scale should result in a
decimal outcome with the same precision/scale. however, i run the following
in query analyzer:
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
RESULT> 523.475511
if i convert @.mult to a float this all works as expected. whats going on her
e?
many thanks
kh> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
Try using a lower precision.
declare @.price decimal(18,10)
declare @.mult decimal(18,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
go
Here is a very interesting script, from Steve Kass, to see the p and s of
the result.
http://www.microsoft.com/technet/co...>
6&sloc=en-us
AMB
"kh" wrote:

> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale. however, i run the followin
g
> in query analyzer:
> declare @.price decimal(38,10)
> declare @.mult decimal(38,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> RESULT> 523.475511
> if i convert @.mult to a float this all works as expected. whats going on h
ere?
> many thanks
> kh
>|||lovely. thanks.
kh
"Alejandro Mesa" wrote:
> Precision, Scale, and Length
> http://msdn.microsoft.com/library/d... />
b_8rc5.asp
> Try using a lower precision.
> declare @.price decimal(18,10)
> declare @.mult decimal(18,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> go
> Here is a very interesting script, from Steve Kass, to see the p and s of
> the result.
> http://www.microsoft.com/technet/co...
c46&sloc=en-us
>
> AMB
>
> "kh" wrote:
>|||Another way to make it work, without changing the precision of your base
numbers...
Specifically cast your result, either in your SQL statement, or assign it to
a variable which has the proper precision.
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
declare @.Result decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
set @.Result = @.price / @.mult
select @.price / @.mult
, cast(@.price / @.mult as decimal(38,10))
, @.Result
go
"kh" <kh@.newsgroups.nospam> wrote in message
news:92EE84D6-BEB4-419A-9054-76EF39B69608@.microsoft.com...
> lovely. thanks.
> kh
> "Alejandro Mesa" wrote:
>
rules,
a
http://msdn.microsoft.com/library/d..._da-db_8rc5.asp[
color=darkred]
of
http://www.microsoft.com/technet/co...5c46&sloc=en-us[color=darkr
ed]
rules,
a
following
on here?|||Jim,
Don't understand, the least significant digits are still truncated and
stuffed with 0s in your solution.
Anyways. Try this and check out the results. Is there a pattern that I am
not able to keep my finger on... there are only 6 places of decimal. or
rather ... I dunno.
P.S: Jim, Chance for u to fuel your ego :)
declare @.price decimal(38,10)
declare @.mult1 decimal(38,10)
declare @.mult2 decimal(38,10)
declare @.mult3 decimal(38,10)
set @.price = 5.2347551174
set @.mult1 = 0.01
set @.mult2 = 0.001
set @.mult3 = 0.0001
select @.price / @.mult1,
@.price / @.mult2,
@.price / @.mult3|||So they are...
Not sure what I was thinking when I looked at that yesterday. I would have
sworn the digits were there, but clearly they are not.
I guess the moral of the story is not simply casting everything when you do
math in SQL Server, but insuring that you only use the precision you
actually need.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:651C58FC-11D3-480D-A185-DDC6CDB1E5DF@.microsoft.com...
> Jim,
> Don't understand, the least significant digits are still truncated and
> stuffed with 0s in your solution.
> Anyways. Try this and check out the results. Is there a pattern that I am
> not able to keep my finger on... there are only 6 places of decimal. or
> rather ... I dunno.
> P.S: Jim, Chance for u to fuel your ego :)
> declare @.price decimal(38,10)
> declare @.mult1 decimal(38,10)
> declare @.mult2 decimal(38,10)
> declare @.mult3 decimal(38,10)
>
> set @.price = 5.2347551174
> set @.mult1 = 0.01
> set @.mult2 = 0.001
> set @.mult3 = 0.0001
>
> select @.price / @.mult1,
> @.price / @.mult2,
> @.price / @.mult3
>

Decimal Data Type losing scale?

I'm trying to update a table that has decimal values. They are defined at
precision of 15 and scale of 2.
When I use a T-Sql update query, I'm sending a value "with pennies", but the
table is only reflecting the integer portion. I've even tried
UPDATE (myTableName)
SET myCost = CAST(@.Cost AS decimal(15,2))
without success.
When I step through the code of my VB.NET program and view the value of item
I've added to the parameters collection of my update query command object, I
DO SEE the pennies. When the query has executed, they aren't in the table.
I CAN TYPE the pennies into the record in the table with Enterprise Mgr. An
d
I can retrieve them with my program. But I can't send new values with
pennies and get them respected in the new table values.
I have seen in Books on Line that we are supposed to explicitly CAST our
decimal values. But shouldn't this take care of it?Hello Q,
When you run profiler what do you see the values being sent as? If you have
your precision and scale not matching exactly in your VB.NET application
it can cause it to send it incorrectly to the database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm trying to update a table that has decimal values. They are
> defined at precision of 15 and scale of 2.
> When I use a T-Sql update query, I'm sending a value "with pennies",
> but the
> table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
> without success.
> When I step through the code of my VB.NET program and view the value
> of item I've added to the parameters collection of my update query
> command object, I DO SEE the pennies. When the query has executed,
> they aren't in the table. I CAN TYPE the pennies into the record in
> the table with Enterprise Mgr. And I can retrieve them with my
> program. But I can't send new values with pennies and get them
> respected in the new table values.
> I have seen in Books on Line that we are supposed to explicitly CAST
> our decimal values. But shouldn't this take care of it?
>|||Is it possible that you've failed to specify precision and scale
for your decimal parameter? The default precision and scale for
a decimal parameter is precision 18, scale 0. If this doesn't seem
to help, could you post the relevant VB.NET code dealing with
the parameter?
Steve Kass
Drew University
Q Johnson wrote:

>I'm trying to update a table that has decimal values. They are defined at
>precision of 15 and scale of 2.
>When I use a T-Sql update query, I'm sending a value "with pennies", but th
e
>table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
>without success.
>When I step through the code of my VB.NET program and view the value of ite
m
>I've added to the parameters collection of my update query command object,
I
>DO SEE the pennies. When the query has executed, they aren't in the table.
>I CAN TYPE the pennies into the record in the table with Enterprise Mgr. A
nd
>I can retrieve them with my program. But I can't send new values with
>pennies and get them respected in the new table values.
>I have seen in Books on Line that we are supposed to explicitly CAST our
>decimal values. But shouldn't this take care of it?
>
>

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19,12) and my result gets rounded around the 5th place of scale. I've tried CASTing, changing sizes on the column(s) and I still seem to get the rounding.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error converting numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ([factorAID] [int] IDENTITY (1, 1) NOT NULL ,[factorA_amt] [decimal](38, 12) NULL ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorB] ([factorBID] [int] IDENTITY (1, 1) NOT NULL ,[factorB_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorC] ([factorCID] [int] IDENTITY (1, 1) NOT NULL ,[factorC_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorD] ([factorDID] [int] IDENTITY (1, 1) NOT NULL ,[factorD_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
FWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my calculations come out correct. I guess that the arithmetic of the table shown in BOL (and below) really needs to be thought through (although I saw a post here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19
,12) and my result gets rounded around the 5th place of scale. I've tried C
ASTing, changing sizes on the column(s) and I still seem to get the rounding
.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error convertin
g numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I
tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ( [factorAID] [int] IDENTITY (1
, 1) NOT NULL , [factorA_amt] [decimal](38, 12) NULL ON [PRIMAR
Y]
GO
CREATE TABLE [dbo].[factorB] ( [factorBID] [int] IDENTITY (1
, 1) NOT NULL , [factorB_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorC] ( [factorCID] [int] IDENTITY (1
, 1) NOT NULL , [factorC_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorD] ( [factorDID] [int] IDENTITY (1
, 1) NOT NULL , [factorD_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS D
ECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GOFWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my cal
culations come out correct. I guess that the arithmetic of the table shown
in BOL (and below) really needs to be thought through (although I saw a post
here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Resu
lt scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1
+ s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 +
p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a resu
lt precision is greater than 38, the corresponding scale is reduced to preve
nt the integral part of a result from being truncated

Friday, March 9, 2012

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Wednesday, March 7, 2012

Decimal field is rounding up my numbers

decimal division precision

Decimal Data Type losing scale?

Decimal column multiplication is rounding

Decimal column multiplication is rounding

sql permissions

Blog Archive

About Me