Data looking different when imported to temporary

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)

Data looking different when imported to temporary

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 13:10:53

Hi,
I am using a temporary table in a stored procedure to import tables in a join. One of the field in the original table is type money. Some of the values in this field shows value 0. However those values are showing .00 in the same field in the temporaty table. I have decalared the field in the temporary table as money. The problem is I am using a logic where if this field value is 0 then a coresponding field will be 0. Here during the check the .00 value is being overlooked. The .00 value should actually show 0.
Any help is appreciated. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 13:32:42

Why dont you have the same datatype in temp table also?

Madhivanan

Failing to plan is Planning to fail

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 14:18:21

I do have the same datatype in the temp table i.e. money. So, I am wondering why 0 in the original table that has money as datatype is diplaying as .00 in the temp table which has also money datatype for this field. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 14:26:23

Please post the code so that we can help you better.

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

Subscribe to my blog

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 14:35:47

OK Tara,
Here is a part of code. Hope this helps.

The first one is a script generated from original table.
CREATE TABLE [dbo].[dbo_PWOCosting] (

[TotalToolingCost] [money] NULL ,
....
)

select TotalToolingCost from dbo_PWOCosting where ...

Result 0

create table #tmpApproval1(
totaltoolingcost money,
....

)

Select TotalToolCost from #tempApproval1 where ...

Result .0000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-11 : 14:38:21

That doesn't help.

If you can't post your actual code or if it's too complex to post, then please come up with a simple example which shows your issue so that we can test on our own machines.

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

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-11 : 14:44:32

How do you get 0 for money column?

declare @m money
set @m=0
select @m

Did you post the full code?

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-11 : 14:45:44

By Default Money datatype would always have precision with it, probably in the select statement
you an remove it using the convert function .. but internally it would be having
the precision.. if you dont give any precision value then by default it would be .00

Chirag

http://www.chirikworld.com

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 15:07:41

OK folks I checked the original table and found out that the default value is stated as (0). That's why I am seeing value 0 in the above field. Now if I want to have this field in the temporary table store 0 instead of .0000 how do I define the field with money. Following is part of a stored procedure.
THE FOLLOWING IS FIRST PART OF STORED PROCEDURE THAT I AM USING:

CREATE proc sp_update_dbo_PWO_Status_7_single_test

@userprefix varchar(10),
@usernumber varchar(10),
@userrevision varchar(10),
@userpricerevision varchar (10),
@userscenario int

as
/* THE FOLLOWING PROCEDURE USES BUSINESS RULES TO UPDATE THE APPROVALREQD FIELD IN
THE STATUS TABLE.APPROVALREQD FIELD IT IS Y(YES) OR N(NO)
*/
BEGIN
SET NOCOUNT ON
--Make a temporary table that contains all the necessary fields for Approval checkup
create table #tmpApproval1(
prefix varchar(4),
number varchar(4),
revision varchar(4),
pricerevision varchar(4),
scenario int,
recommendedshipsite varchar(15),
terms varchar(6),
inventory varchar(3),
label varchar(3),
prepaidfreight varchar(3),
modifyexistingalloyditem varchar(3),
materialreview varchar(1),
materialtype varchar(20),
materialguage float(8),
medical varchar(3),
perimetertrim varchar(15),
hanghole varchar(25),
trymeportholes varchar(25),
guillotine varchar(3),
engraving varchar(3),
regulatoryrequirement varchar(40),
annualvolumeper1000 float(8),
firstyearrevenueperthousand money,
lowestmktpricemsrp money,
factor float(8),
toolmsrpmarketprice money,
totaltoolingcost money,
roi_initial decimal(10,2)
)

-- select * from #tmpApproval1
/* INSERT INTO THE TEMPORARY TABLE ALL THE DATA OUT OF JOINED PWO TABLES */

insert into #tmpApproval1
(prefix
, number
, revision
, pricerevision
, scenario
, recommendedshipsite
, terms
, inventory
, label
, prepaidfreight
, modifyexistingalloyditem
, materialreview
, materialtype
, materialguage
, medical
, perimetertrim
, hanghole
, trymeportholes
, guillotine
, engraving
, regulatoryrequirement
, annualvolumeper1000
,firstyearrevenueperthousand
,lowestmktpricemsrp
,factor
,toolmsrpmarketprice
,totaltoolingcost
,ROI_initial
)

SELECT dbo_PWOProject.Prefix
, dbo_PWOProject.Number
, dbo_PWOProject.Revision
, dbo_PWOProject.PriceRevision
, dbo_PWOMaterialType.Scenario
, dbo_PWOProject.RecommendedShipSite
, dbo_PWOProject.Terms
, dbo_PWOProject.Inventory
, dbo_PWOProject.Label
, dbo_PWOProject.PrepaidFreight
, dbo_PWODescription.ModifyExistingAlloydItem
, dbo_PWOMaterialType.MaterialReview
, dbo_PWOMaterialType.MaterialType
, dbo_PWOMaterialType.MaterialGauge
, dbo_PWOSpecifications.Medical
, dbo_PWOSpecifications.PerimeterTrim
, dbo_PWOSpecifications.HangHole
, dbo_PWOSpecifications.TryMePortHoles
, dbo_PWOSpecifications.Guillotine
, dbo_PWOSpecifications.Engraving
, dbo_PWOSpecifications.RegulatoryRequirement
, dbo_PWOPayback.annualvolumeper1000
, dbo_PWOPayback.firstyearrevenueperthousand
, dbo_PWOPayback.lowestmktpricemsrp
, dbo_PWOPricing.factor
, dbo_PWOPricing.toolmsrpmarketprice
, dbo_PWOCosting.totaltoolingcost
,null

FROM ((dbo_PWOProject LEFT JOIN dbo_PWOSpecifications ON
(dbo_PWOProject.Prefix = dbo_PWOSpecifications.Prefix)
AND (dbo_PWOProject.Number = dbo_PWOSpecifications.Number)
AND (dbo_PWOProject.Revision = dbo_PWOSpecifications.Revision)
AND (dbo_PWOProject.PriceRevision = dbo_PWOSpecifications.PriceRevision))
LEFT JOIN dbo_PWODescription ON
(dbo_PWOProject.Prefix = dbo_PWODescription.Prefix)
AND (dbo_PWOProject.Number = dbo_PWODescription.Number)
AND (dbo_PWOProject.Revision = dbo_PWODescription.Revision)
AND (dbo_PWOProject.PriceRevision = dbo_PWODescription.PriceRevision))
LEFT JOIN dbo_PWOMaterialType ON
(dbo_PWOProject.Prefix = dbo_PWOMaterialType.Prefix)
AND (dbo_PWOProject.Number = dbo_PWOMaterialType.Number)
AND (dbo_PWOProject.Revision = dbo_PWOMaterialType.Revision)
AND (dbo_PWOProject.PriceRevision = dbo_PWOMaterialType.PriceRevision)
LEFT JOIN dbo_PWOPayback ON
(dbo_PWOProject.Prefix = dbo_PWOPayback.Prefix)
AND (dbo_PWOProject.Number = dbo_PWOPayback.Number)
AND (dbo_PWOProject.Revision = dbo_PWOPayback.Revision)
AND (dbo_PWOProject.PriceRevision = dbo_PWOPayback.PriceRevision)
LEFT JOIN dbo_PWOPricing ON
(dbo_PWOProject.Prefix = dbo_PWOPricing.Prefix)
AND (dbo_PWOProject.Number = dbo_PWOPricing.Number)
AND (dbo_PWOProject.Revision = dbo_PWOPricing.Revision)
AND (dbo_PWOProject.PriceRevision = dbo_PWOPricing.PriceRevision)
LEFT JOIN dbo_PWOCosting ON
(dbo_PWOProject.Prefix = dbo_PWOCosting.Prefix)
AND (dbo_PWOProject.Number = dbo_PWOCosting.Number)
AND (dbo_PWOProject.Revision = dbo_PWOCosting.Revision)
AND (dbo_PWOProject.PriceRevision = dbo_PWOCosting.PriceRevision)

where dbo_PWOProject.Prefix = @userprefix
and dbo_PWOProject.Number = @usernumber
and dbo_PWOProject.Revision = @userrevision
and dbo_PWOProject.Pricerevision = @userpricerevision
and dbo_PWOMaterialType.Scenario = @userscenario

--select * from #tmpApproval1

/* NOW DO A GROUP BY AND ORDER BY TO GET ALL THE ROWS THAT MATCHES */
SELECT #tmpApproval1.prefix, #tmpApproval1.number, #tmpApproval1.revision, #tmpApproval1.pricerevision, #tmpApproval1.scenario, #tmpApproval1.recommendedshipsite, #tmpApproval1.terms, #tmpApproval1.inventory, #tmpApproval1.label, #tmpApproval1.prepaidfreight, #tmpApproval1.modifyexistingalloyditem, #tmpApproval1.materialreview, #tmpApproval1.materialtype, #tmpApproval1.materialguage, #tmpApproval1.medical, #tmpApproval1.perimetertrim, #tmpApproval1.hanghole, #tmpApproval1.trymeportholes, #tmpApproval1.guillotine, #tmpApproval1.engraving, #tmpApproval1.regulatoryrequirement, #tmpApproval1.annualvolumeper1000, #tmpApproval1.firstyearrevenueperthousand, #tmpApproval1.lowestmktpricemsrp, #tmpApproval1.factor, #tmpApproval1.toolmsrpmarketprice, #tmpApproval1.totaltoolingcost,#tmpApproval1.ROI_initial
INTO #tmpApproval1a
FROM #tmpApproval1
GROUP BY #tmpApproval1.prefix, #tmpApproval1.number, #tmpApproval1.revision, #tmpApproval1.pricerevision, #tmpApproval1.scenario, #tmpApproval1.recommendedshipsite, #tmpApproval1.terms, #tmpApproval1.inventory, #tmpApproval1.label, #tmpApproval1.prepaidfreight, #tmpApproval1.modifyexistingalloyditem, #tmpApproval1.materialreview, #tmpApproval1.materialtype, #tmpApproval1.materialguage, #tmpApproval1.medical, #tmpApproval1.perimetertrim, #tmpApproval1.hanghole, #tmpApproval1.trymeportholes, #tmpApproval1.guillotine, #tmpApproval1.engraving, #tmpApproval1.regulatoryrequirement, #tmpApproval1.annualvolumeper1000, #tmpApproval1.firstyearrevenueperthousand, #tmpApproval1.lowestmktpricemsrp, #tmpApproval1.factor, #tmpApproval1.toolmsrpmarketprice, #tmpApproval1.totaltoolingcost,#tmpApproval1.ROI_initial
ORDER BY #tmpApproval1.prefix, #tmpApproval1.number, #tmpApproval1.revision, #tmpApproval1.pricerevision, #tmpApproval1.scenario, #tmpApproval1.recommendedshipsite, #tmpApproval1.terms, #tmpApproval1.inventory, #tmpApproval1.label, #tmpApproval1.prepaidfreight, #tmpApproval1.modifyexistingalloyditem, #tmpApproval1.materialreview, #tmpApproval1.materialtype, #tmpApproval1.materialguage, #tmpApproval1.medical, #tmpApproval1.perimetertrim, #tmpApproval1.hanghole, #tmpApproval1.trymeportholes, #tmpApproval1.guillotine, #tmpApproval1.engraving, #tmpApproval1.regulatoryrequirement, #tmpApproval1.annualvolumeper1000, #tmpApproval1.firstyearrevenueperthousand, #tmpApproval1.lowestmktpricemsrp, #tmpApproval1.factor, #tmpApproval1.toolmsrpmarketprice, #tmpApproval1.totaltoolingcost,#tmpApproval1.ROI_initial

select top 1* from #tmpApproval1a

gamaz
Posting Yak Master

104 Posts

Posted - 2008-06-11 : 15:33:26

Folks Thanks for your help. I think I got it. When I generated another table whose structure is same as the temporary table but I can see the values stored in this table I am seeing 0 as well. In sql server qa it is diaplyed as .00 though. Best regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources