Insert Question.

Hello. I am extending an existing proc and encountering a strange issue. I
hope someone can shed some light on this. Here's my problem :
insert into : table with decimal(14,2) columns ( where you see math )
sql statement :
TRUNCATE TABLE dbo.tbl_met_wos
INSERT INTO dbo.tbl_met_wos
SELECT
b.TopDivision as Division,
b.TopDivisionName as DivisionName,
b.Div as Department,
b.DivName as DepartmentName,
b.Dept as Class,
b.DeptName as ClassName,
a.fponhandTW,
a.fponhandMTD,
a.fponhandTQ,
a.fponhandTS,
a.fponhandTY,
c.mdonhandTW,
c.mdonhandMTD,
c.mdonhandTQ,
c.mdonhandTS, --STD
c.mdonhandTY,
d.FPSalesTW,
d.FPSalesMTD,
d.FPSalesTQ,
d.FPSalesSTD,
d.FPSalesTY,
e.MDSalesTW,
e.MDSalesMTD,
e.MDSalesTQ,
e.MDSalesSTD,
e.MDSalesTY,
--RH Added.
--cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in (0,null)
then 1 else d.FPSalesTW end) AS decimal(14,2)),
'1.25',
Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
then 1 else e.MDSalesTW end) AS varchar),
(a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
else d.FPSalesMTD end,
(c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
else e.MDSalesMTD end,
Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
(c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
else e.MDSalesTQ end,
(a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
else d.FPSalesSTD end,
--(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then 1
else e.MDSalesSTD end,
'1.37',
(a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
else d.FPSalesTY end,
(c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
else e.MDSalesTY end
FROM #HIERARCHY b
LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
a.Department
LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
c.Department
LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department =
d.Department
LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
e.Department
ORDER BY
b.Div,
b.Dept
as you can see, this is not the hottest proc in the world, but support sucks
sometimes :) anyhow, when this insert statement runs, It will round off to
whole numbers where you see math in the fields. Where you see '1.25' and
'1.37' they insert fine as a decimal(14,2) when you select from the table. I
have tryed converting and casting the entire field like :
Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
then 1 else e.MDSalesTQ end AS Decimal(14,2)
but it still rounds off to a whole number. if it were trying to insert
100.65 I get 101.00 when I select from the table.
Any IDEAS? I'm really with this one.
Robert H"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
>
...

> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
>
The sum or product of two decimals requires more precision than the
individual operands. As the required precision approaches 36, scale is
trimmed. So after a few operations your results quickly become converted to
decimal(36,0) unless you are diligent about converting intermediate results
to remove unneeded precision.
If you are sticking with decimal(14,2), just convert each intermediate
result to decimal(16,4), or something, to preserve your scale.
See:
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||Robert H wrote:
> Hello. I am extending an existing proc and encountering a strange
> issue. I hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW
> in(0,null) then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null)
> then 1 else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null)
> then 1 else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ
> in(0,null) then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then
> 1 else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null)
> then 1 else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null)
> then 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then
> 1 else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then
> 1 else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND
> b.Department = a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND
> b.Department = c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND
> b.Department = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND
> b.Department = e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but
> support sucks sometimes :) anyhow, when this insert statement runs,
> It will round off to whole numbers where you see math in the fields.
> Where you see '1.25' and '1.37' they insert fine as a decimal(14,2)
> when you select from the table. I have tryed converting and casting
> the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ
> in(0,null) then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
Don't use integer type values in the calculation unless you want an
integer result. Make sure all integer values are first converted/cast to
decimal (14, 2) first (you can do this inline). If it's a hard-coded
value, then use "0.00" instead of "0".
Also, the TRUNCATE TABLE operation can only be run by system
administrators, table owner, db_owner, and db_ddladmin. So you won't be
able to grant execute on the procedure to users without those rights.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks Guys ! I now know a little more about sql precision! The solution
to the problem based on what you guys have said is to convert
each field to a real data type. This solved all problems. that truncate
table was allready in the proc, and was in production so Someone gave it the
proper access.
Thanks!
Robert H
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
> then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
> else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
> else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
> then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
> else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
> else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then
> 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
> else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
> else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
> a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
> c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department
> = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
> e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but support
> sucks sometimes :) anyhow, when this insert statement runs, It will round
> off to whole numbers where you see math in the fields. Where you see
> '1.25' and '1.37' they insert fine as a decimal(14,2) when you select from
> the table. I have tryed converting and casting the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
> then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
>
>|||Robert H wrote:
> Thanks Guys ! I now know a little more about sql precision! The
> solution to the problem based on what you guys have said is to convert
> each field to a real data type. This solved all problems. that
> truncate table was allready in the proc, and was in production so
> Someone gave it the proper access.
No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eySIpGYrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Robert H wrote:
> No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
>
Actually, using floating point in a calculation is a very reasonable thing
to to. It frees you from having to manually adjust the precision and scale
after each operation. At the end you can round off and convert back to a
decimal type, knowing that th accumulation of error in the calculation was
minimized by the floating point arithmetic.
David