Why a decimal type field returns integer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Why a decimal type field returns integer

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-07-11 : 18:46:56

Hi,
I am in the process of building a stored procedure. Some of the datatype are decimals which corresponds to table field types. I am using a temptable to store intermediate values. When I am executing this stored procedure and getting the output from the temp table the values are showing up to be numeric instead of decimal.

e.g

QtyPer should be 0.01666 but I am getting 0
FistMaterialUnitCost should be 4.934248 but I am getting 5
FirstTotalLaborCost should be 2.27499999 but I am getting 2
FirstTotalUnitCost should be 7.20924799 but I am getting 7

I am not sure why the temptable is throwing out numeric values here. Any help is appreciated. Thanks.

CODE:

create proc RawMaterialCostByFamilyCode_source1
@FamilyCode varchar(50)
AS
BEGIN
SET NOCOUNT ON
--Make a temporary table
create table #temp1(

finishedpartno varchar(50)

,projectno varchar(50)

,estimateno varchar(50)

,estimatedesc varchar(255)

,estimatetime datetime

,customername varchar(50)

,engineerName varchar(50)

,plantname varchar(50)

,familycode varchar(50)

,qtyper decimal(9)

,quoteunitcost decimal(9)

,firstreleaseqty int

,firstmaterialunitcost decimal(9)

,firsttotallaborcost decimal(9)

,firsttotalunitcost decimal(9)

,lastreleaseqty bigint

,lastmaterialunitcost decimal(9)

,lasttotallaborcost decimal(9)

,lasttotalunitcost decimal(9)

,project_num varchar(50)

,lastestimate# varchar(50)

)

insert into #temp1

SELECT *
FROM (SELECT [FG Part#] = FinishedPartNo
, [Project#] = ProjectNo
, EstimateNo
, EstimateDesc
, EstimateTime
, CustomerName
, EngineerName
, PlantName
, FamilyCode
, QtyPer
, QuoteUnitCost
, FirstReleaseQty = FIRST.ReleaseQty
, FirstMaterialUnitCost = FER.MaterialUnitCost
, FirstTotalLaborCost = IsNull(FER.FixedUnitCost, 0) + IsNull(FER.VariableUnitCost, 0) + IsNull(FER.LaborUnitCost, 0)
+ IsNull(FER.SetupUnitCost, 0)
, FirstTotalUnitCost = IsNull(FER.FixedUnitCost, 0) + IsNull(FER.VariableUnitCost, 0)
+ IsNull(FER.LaborUnitCost, 0) + IsNull(FER.SetupUnitCost, 0) + IsNull(FER.MaterialUnitCost, 0)
, LastReleaseQty = Last.ReleaseQty
, LastMaterialUnitCost = LER.MaterialUnitCost
, LastTotalLaborCost = IsNull(LER.FixedUnitCost, 0) + IsNull(LER.VariableUnitCost, 0)
+ IsNull(LER.LaborUnitCost, 0) + IsNull(LER.SetupUnitCost, 0)
, LastTotalUnitCost = IsNull(LER.FixedUnitCost, 0)
+ IsNull(LER.VariableUnitCost, 0) + IsNull(LER.LaborUnitCost, 0) + IsNull(LER.SetupUnitCost, 0) + IsNull(LER.MaterialUnitCost, 0)

FROM Estimate1 E JOIN
(SELECT EstimateKey, ReleaseQty = MIN(FinishedQtyBreak)
FROM tblEstimateRelease
GROUP BY EstimateKey) AS FIRST ON FIRST.EstimateKey = E.EstimateKey JOIN
tblEstimateRelease FER ON FER.EstimateKey = E.EstimateKey AND FER.FinishedQtyBreak = FIRST.ReleaseQty JOIN
(SELECT EstimateKey, ReleaseQty = MAX(FinishedQtyBreak)
FROM tblEstimateRelease
GROUP BY EstimateKey) AS LAST ON LAST.EstimateKey = E.EstimateKey JOIN
tblEstimateRelease LER ON LER.EstimateKey = E.EstimateKey AND LER.FinishedQtyBreak = LAST.ReleaseQty
WHERE E.Done = 1 AND IsNull(E.FinishedPartNo, '') <> '') DTL INNER JOIN
(SELECT ProjectNo, LastEstimateNo = MAX(EstimateNo)
FROM tblEstimate
WHERE Done = 1 AND IsNull(FinishedPartNo, '') <> ''
GROUP BY ProjectNo) LASTEST ON LASTEST.ProjectNo = DTL.Project# AND LASTEST.LastEstimateNo = DTL.EstimateNo

group by [FG Part#]

, [Project#]
, EstimateNo
, EstimateDesc
, EstimateTime
, CustomerName
, EngineerName
, PlantName
, FamilyCode
, QtyPer
, QuoteUnitCost
, FirstReleaseQty
, FirstMaterialUnitCost
, FirstTotalLaborCost
, FirstTotalUnitCost
, LastReleaseQty
, LastMaterialUnitCost
, LastTotalLaborCost
, LastTotalUnitCost
, projectno
, lastestimateno

select * from #temp1
where finishedpartno = '022-7461-000' and familycode = '1999'

end

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-11 : 19:38:48

Multiply your int and bigint columns with 1.0.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 01:17:22

i think its because of implicit conversion. Multiplying by 1.0 ensures it returns results with the decimals. So do as suggested by Tara
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-12 : 10:27:51

Hello,
,qtyper decimal(9)
am i right, when i say that in case of missing the scale value then the default scale i 0?
Because of this it could be, that it's not possible to take values for the right side of the decimal point.
It maybe decimal(14,5)

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

gamaz2
Starting Member

31 Posts

Posted - 2008-07-14 : 12:36:28

Thanks Tare, visakh16 for your help. I appreciate it. Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources