SQL Help -Arithmetic overflow error converting num

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

SQL Server Development (2000)

SQL Help -Arithmetic overflow error converting num

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-08-10 : 12:28:48

I'm trying to sort the distance from a specified long and lat, but I get Arithmetic overflow error converting numeric to data type numeric.

Any ideas? Here is what I did...

SELECT ROUND(ACOS(SIN(27.846419 / 57.2958) * SIN(LATITUDE / 57.2958) + COS(27.846419 / 57.2958) * COS(LATITUDE / 57.2958)
* COS(LONGITUDE / 57.2958 - - (082.301249 / 57.2958))) * 3963, 2) AS distance
FROM ZipCodes
ORDER BY distance

I tried this too....

SELECT CAST(ROUND((ACOS((SIN(+27.846419/57.2958) * SIN(latitude/57.2958)) +
(COS(+27.846419/57.2958) * COS(latitude/57.2958) *
COS(longitude/57.2958 - -082.301249/57.2958))))
* 3963, 2) As int) AS distance
FROM zipcodes
ORDER BY distance

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-10 : 13:40:44

do you have some table DDL and test data we can work with?

-ec

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-10 : 13:47:38

Well, you're not going to able to ODER BY an alias like that...

Follow and read the hint link below...

And there's a boat load of articles here at sql team...you should try a search

http://www.johnsample.com/articles/GeocodeWithSqlServer.aspx

I found this on the home page

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-10 : 14:59:39

quote:
Originally posted by X002548

Well, you're not going to able to ODER BY an alias like that...

that is a much needed feature btw.

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-08-10 : 15:06:07

This is working well, but cant sort it.

SELECT ROUND(3959 * ACOS(SIN(27.846419 / 57.3) * SIN(LATITUDE / 57.3) + COS(27.846419 / 57.3) * COS(LATITUDE / 57.3) * COS((- 82.301249 - LONGITUDE)
/ 57.3)), 0) AS exactDistance, ZIP, CITY, STATE
FROM ZipCodes

I even tried
SELECT ROUND(3959 * ACOS(SIN(27.846419 / 57.3) * SIN(LATITUDE / 57.3) + COS(27.846419 / 57.3) * COS(LATITUDE / 57.3) * COS((- 82.301249 - LONGITUDE)
/ 57.3)), 0) AS exactDistance, ZIP, CITY, STATE
FROM ZipCodes
ORDER BY ROUND(3959 * ACOS(SIN(27.846419 / 57.3) * SIN(LATITUDE / 57.3) + COS(27.846419 / 57.3) * COS(LATITUDE / 57.3) * COS((- 82.301249 - LONGITUDE)
/ 57.3)), 0)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-10 : 15:56:47

Maybe you could try:
ORDER BY 1

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources