SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Decimal Precision
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  11:28:16  Show Profile
We have had problem with the decimals precision within real values. For example, Any values are stored as shown below:

Value ***** Instead of
6.0999999 ---> 6.00
28.120001 ---> 28.12
0.91000003 ---> 1.00
9.0000004E-2 ---> 0.00.

What could be wrong ?



L.L.

Edited by - l.lopez on 07/06/2004 11:37:18

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/06/2004 :  11:59:21  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
You need to store the values as DECIMAL(6,2) if you want to have it how your "Instead of" column is using it. You are now storing it as a float, which would be a real mess if you're doing accounting or something with it. Real in SQL Server is just a float.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  12:11:15  Show Profile
Ok, What could be happen if I change the type of field?... Are affected the values in the field?... We have 64 fields declared like real. What should We do?

L.L.

Edited by - l.lopez on 07/06/2004 13:24:59
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/06/2004 :  14:03:26  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Well, before you do that select the values out of them with

SELECT CAST(column AS DECIMAL(9,2))

for example and see if this is what really want to do. If so, then by all means have at it.


CREATE TABLE #columntest(
	itsanumberalright REAL)

INSERT #columntest(itsanumberalright)
	SELECT 14.12388011

SELECT * FROM #columntest

ALTER TABLE #columntest ALTER COLUMN itsanumberalright DECIMAL(9,2)

SELECT * FROM #columntest



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/06/2004 :  14:36:52  Show Profile
Before you do ANYTHING...backup the database



Brett

8-)
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  14:39:17  Show Profile
I think this will help me too much. I go to try it.

Thanks a lot by the support

L.L.
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  15:47:19  Show Profile
By the way, the declaration DECIMAL(9,2) means 9 integer and 2 decimals or 7 integer and 2 decimals?. I ask this because in Oracle DB means 7 integer and 2 decimals, and I am not sure in SQL Server.

Thanks in advance

L.L.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/06/2004 :  15:53:30  Show Profile
Do you have Query Analyzer open? It's kinda easy to test if you do...


DECLARE @x decimal(9,2)
SELECT @x = 123456789.1234
SELECT @x
GO
DECLARE @x decimal(9,2)
SELECT @x = 1234567.12
SELECT @x
GO




Brett

8-)
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  15:59:10  Show Profile
Yes, I have it. The First one is wrong, I just realized that works like Oracle.

Thanks again Brett.

L.L.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/06/2004 :  16:18:34  Show Profile
But not like COBOL....


DECLARE @x decimal(9,2)
SELECT @x = 123456789.12
SELECT @x
GO



What about vb?



Brett

8-)
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  16:31:32  Show Profile
OK, In VB I used to declarate this kind of data like Single or Double. To be honest, I have never worked with COBOL.

L.L.

Edited by - l.lopez on 07/06/2004 16:32:35
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/06/2004 :  17:12:13  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Ahhh, so sad. (NOT) You aren't missing anything. :) Hey, hiring any DBA's in Venezuela? Are you in Caracas. I need a DBA position in Margarita. WOO HOO!!!!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/06/2004 :  17:44:14  Show Profile
In Our Country there is not enough hiring, It is difficult due to the political problems. I am from Maracaibo. Would you like to find a job in Margarita?

L.L.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/06/2004 :  17:48:23  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I would love to. I have a couple friends from Maracaibo. I'm also aware of the economical and political situation right now. Maybe some day though. It's a beautiful country with a lot of resources. I don't think your current political leaders would like me though. :)



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/07/2004 :  10:21:08  Show Profile
I think you guys want a job in Margaritaville....

But I doubt there's any dba work there....however if you know how to work a blender.....

or roll a spliff

you may be in bd'ness



Brett

8-)
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/07/2004 :  15:13:13  Show Profile
Hey Brett, My english is not as good. What does it mean "to work a blender..... or roll a spliff"? and "bd'ness"?


This is too deeper for me...

Regards


L.L.

Edited by - l.lopez on 07/07/2004 15:15:05
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/07/2004 :  19:05:23  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Work a blender....get a job as a bartender or something making Margarita's, which is what they all drink in huge amounts. The others......don't worry, it's not your English. We don't know what he's saying half the time either. We just ignore him. You know....figure he's talking to his other self again.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/08/2004 :  08:32:46  Show Profile
AAAhhh Ok, Brett is so funny. That's good. At least he enjoys his work. On the other hand, I tell you that today is my birthday. You should help me to change the 64 real fields to decimal. I just have changed like 10.

Regards

L.L.

Edited by - l.lopez on 07/08/2004 08:38:12
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/08/2004 :  08:37:55  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
DROP DATABASE database_name

That should fix you up.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

l.lopez
Starting Member

Venezuela
18 Posts

Posted - 07/08/2004 :  08:43:56  Show Profile
You are so funny, too. To be honest, I think that It's the best thing I can do...

L.L.
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 07/08/2004 :  08:45:38  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
Oh, and Happy Birthday.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000