Devious Decimal Doldrums Developing

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)

Devious Decimal Doldrums Developing

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-10-26 : 13:54:17

My asteroid table is getting skewered. I plug in the figures, but the results expand my figures incredibly. For example: Quetzalcoatl shows an AU of 2.542, but the result shows it closer to the Sun by .0000000000000002. I know it didn't move that much in the time it took me to type this. How can I fix this? Here's my input and the text output.

--Numbered Near Earth Asteroids and Mars-Crossers

DROP TABLE [Fowler].[dbo].[Asteroids]

CREATE TABLE [Fowler].[dbo].[Asteroids]
(Number nvarchar (05) NULL,
Designation nvarchar (20) NULL,
Distance_AU float NULL,
Type nvarchar (20) NULL)
GO
INSERT Asteroids VALUES ('887 ','Alinda ','2.484','Amor 3 ')
INSERT Asteroids VALUES ('1915 ','Quetzalcoatl','2.542','Amor 3 ')
INSERT Asteroids VALUES ('2608 ','Sineca ','2.503','Amor 3 ')
INSERT Asteroids VALUES ('3360 ','1981 VA ','2.468','Apollo 3 ')
INSERT Asteroids VALUES ('4179 ','Toutatis ','2.510','Apollo 3 ')
INSERT Asteroids VALUES ('5836 ','1993 MF ','2.445','Amor 3 ')
INSERT Asteroids VALUES ('6318 ','Conkrite ','2.510','Mars-crosser')
INSERT Asteroids VALUES ('6322 ','1991 CQ ','2.516','Mars-crosser')
INSERT Asteroids VALUES ('6489 ','Golevka ','2.493','Apollo 3 ')
INSERT Asteroids VALUES ('6491 ','1991 OA ','2.509','Amor 3 ')
INSERT Asteroids VALUES ('7092 ','Cadmus ','2.524','Apollo 3 ')
INSERT Asteroids VALUES ('7345 ','Happer ','2.450','Mars-crosser')
INSERT Asteroids VALUES ('8201 ','1994 AH2 ','2.526','Apollo 3 ')
INSERT Asteroids VALUES ('8709 ','Kadlu ','2.534','Mars-crosser')
INSERT Asteroids VALUES ('13551','1992 FL1 ','2.527','Mars-crosser')
INSERT Asteroids VALUES ('16588','1992 ST ','2.554','Mars-crosser')
INSERT Asteroids VALUES ('19356','1997 GH3 ','2.507','Amor 3 ')
INSERT Asteroids VALUES ('30825','1990 TG1 ','2.439','Apollo 3 ')
GO

SELECT * FROM [Fowler].[dbo].[Asteroids] GO

Number Designation Distance_AU Type
------ -------------------- ----------------------------------------------------- --------------------
887 Alinda 2.484 Amor 3
1915 Quetzalcoatl 2.5419999999999998 Amor 3
2608 Sineca 2.5030000000000001 Amor 3
3360 1981 VA 2.468 Apollo 3
4179 Toutatis 2.5099999999999998 Apollo 3
5836 1993 MF 2.4449999999999998 Amor 3
6318 Conkrite 2.5099999999999998 Mars-crosser
6322 1991 CQ 2.516 Mars-crosser
6489 Golevka 2.4929999999999999 Apollo 3
6491 1991 OA 2.5089999999999999 Amor 3
7092 Cadmus 2.524 Apollo 3
7345 Happer 2.4500000000000002 Mars-crosser
8201 1994 AH2 2.5259999999999998 Apollo 3
8709 Kadlu 2.5339999999999998 Mars-crosser
13551 1992 FL1 2.5270000000000001 Mars-crosser
16588 1992 ST 2.5539999999999998 Mars-crosser
19356 1997 GH3 2.5070000000000001 Amor 3
30825 1990 TG1 2.4390000000000001 Apollo 3

(18 row(s) affected)

Thanks!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-26 : 14:01:08

use decimal rather than float

CREATE TABLE [dbo].[Asteroids]
(Number nvarchar (05) NULL,
Designation nvarchar (20) NULL,
Distance_AU decimal(18, 9) NULL,
Type nvarchar (20) NULL)
GO
INSERT Asteroids VALUES ('887 ','Alinda ','2.484','Amor 3 ')
INSERT Asteroids VALUES ('1915 ','Quetzalcoatl','2.54200000000000000','Amor 3 ')
INSERT Asteroids VALUES ('2608 ','Sineca ','2.503','Amor 3 ')
INSERT Asteroids VALUES ('3360 ','1981 VA ','2.468','Apollo 3 ')
INSERT Asteroids VALUES ('4179 ','Toutatis ','2.510','Apollo 3 ')
INSERT Asteroids VALUES ('5836 ','1993 MF ','2.445','Amor 3 ')
INSERT Asteroids VALUES ('6318 ','Conkrite ','2.510','Mars-crosser')
INSERT Asteroids VALUES ('6322 ','1991 CQ ','2.516','Mars-crosser')
INSERT Asteroids VALUES ('6489 ','Golevka ','2.493','Apollo 3 ')
INSERT Asteroids VALUES ('6491 ','1991 OA ','2.509','Amor 3 ')
INSERT Asteroids VALUES ('7092 ','Cadmus ','2.524','Apollo 3 ')
INSERT Asteroids VALUES ('7345 ','Happer ','2.450','Mars-crosser')
INSERT Asteroids VALUES ('8201 ','1994 AH2 ','2.526','Apollo 3 ')
INSERT Asteroids VALUES ('8709 ','Kadlu ','2.534','Mars-crosser')
INSERT Asteroids VALUES ('13551','1992 FL1 ','2.527','Mars-crosser')
INSERT Asteroids VALUES ('16588','1992 ST ','2.554','Mars-crosser')
INSERT Asteroids VALUES ('19356','1997 GH3 ','2.507','Amor 3 ')
INSERT Asteroids VALUES ('30825','1990 TG1 ','2.439','Apollo 3 ')
GO

SELECT * FROM [dbo].[Asteroids] GO

887 Alinda 2.484000000 Amor 3
1915 Quetzalcoatl 2.542000000 Amor 3
2608 Sineca 2.503000000 Amor 3
3360 1981 VA 2.468000000 Apollo 3
4179 Toutatis 2.510000000 Apollo 3
5836 1993 MF 2.445000000 Amor 3
6318 Conkrite 2.510000000 Mars-crosser
6322 1991 CQ 2.516000000 Mars-crosser
6489 Golevka 2.493000000 Apollo 3
6491 1991 OA 2.509000000 Amor 3
7092 Cadmus 2.524000000 Apollo 3
7345 Happer 2.450000000 Mars-crosser
8201 1994 AH2 2.526000000 Apollo 3
8709 Kadlu 2.534000000 Mars-crosser
13551 1992 FL1 2.527000000 Mars-crosser
16588 1992 ST 2.554000000 Mars-crosser
19356 1997 GH3 2.507000000 Amor 3
30825 1990 TG1 2.439000000 Apollo 3

Duane.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-26 : 14:07:16

Agreed on using decimal instead of float.

Float: "Floating point data is approximate; not all values in the data type range can be precisely represented."

The approximate part is the problem.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-10-26 : 14:41:48

Obviously I should be awarded a giant "Duh!" Sorry 'bout the dumb question. I just looked too hard at the problem.

Thanks, Ditch...&....Thanks SQL Goddess!!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources