value differs in display and manipulations

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 2005 Forums

Transact-SQL (2005)

value differs in display and manipulations

Author

Topic

srinisql
Starting Member

9 Posts

Posted - 2009-03-19 : 08:54:02

Dear All,

I have strange experience recently when i try to manipulate values in table in the sp
Actually when i try to execute the select query it shows values in one form.
if the same is used in data manipulation it is calculated in another form.

for eg.

declare @a float
select @a= sum(total) from invoicetable where invno = 'invoice1'
1st query
----------
select @a

2nd query
-----------
select (14166 - ( (1514.52+ 30.29) + (@a)) )

---select (14166 - ( (1514.52+ 30.29) + (12621.19) ) )

Result
-------

(No Column Name)
----------------
12621.19

(No Column Name)
-----------------
-3.63797880709171E-12

Actually the return value of the table is 12621.19 in display (1st query)

but if the same is manipulated in the second query it is formed into different value. (2nd query_

Can Any one clarify me the Fact behind it

Thanks in Advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-19 : 10:12:56

Two things going on for you.

One is that when sql performs MATH it follows some rules to determine the scale and precision of the result. Those rules take into account the datatypes of the operands as well as the specific values (scale and precision) of any constants used.

The other thing is that you are using float. The "approximate", floating point datatype is probably not a good choice when dealing with Invoice amounts (money). Why don't you use the MONEY datatype for that?

The bottom line is if you want a specific scale and precision as a result you will need to CAST/CONVERT the components and/or the final result to the appropriate (precise) datatype.

Be One with the Optimizer
TG

srinisql
Starting Member

9 Posts

Posted - 2009-03-20 : 00:08:53

Hi TG,

Thanks for the Reply. Can me tell how to solve this, i need the data manipulation is done in term of the display value
like the eg above

@a should be manipulated as 12621.19 which is display value (but it is manipulated differently)

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-20 : 08:31:46

I'm not at a server rigtt now but try this change:
declare @a MONEY

What is the datatype of the column [invoiceTable].[total] ?

EDIT:

quote:
Can me tell how to solve this

I did - in the last sentence of my previous post. Did you try it?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources