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