0

I'm trying to get a query to work which returns a list of locId's from the database when fed a long and a lat.

Here's the sql:

eg: lat = "-37.8333300" : lon = "145.000000" : radius = (5 * 0.621371192) ^ 2

SELECT locId,longitude,latitude FROM tbliplocations WHERE (69.1*([longitude]- "&lon&") * cos("&lat&"/57.3))^2 + (69.1*([latitude]- "&lat&"))^2 < "&radius

Here's the error I receive:

The data types float and int are incompatible in the '^' operator.

I'm unsure of a workaround, can anyone point me in the right direction?

Answer:

Using SQL Server 2008 R2

SELECT city FROM tbliplocationsnew WHERE POWER((69.1*([longitude]- "&lon&") * cos("&lat&"/57.3)),2) + POWER((69.1*([latitude]- "&lat&")),2)  < "&radius
Ben
  • 221
  • 1
  • 2
  • 7

2 Answers2

4

Not sure what database you use, but I think that "^2" in SQL does not mean "squared" like in maths. You should use a math "power" function, like POWER(number,2) in SQL Server (since you use VB maybe you use SQL Server ?)

huelbois
  • 6,762
  • 1
  • 19
  • 21
  • There's at least an exception : PostgreSQL treats ^ like the power function it seems (just installed one and checked, after reading some page on the web about arithmetic operator in PostgreSQL: select 10 ^2 returns 100). But not MySQL. Got to check SQL standard. – huelbois Feb 01 '12 at 01:42
  • Thanks Huelbois, that did the trick! Updated question with answer – Ben Feb 01 '12 at 02:24
1

You need to have two of the same data type it's saying. SQL thinks "5" is an int. So, you should be able to trick it into treating it as a float, by putting "5.0" instead.

Jody
  • 8,021
  • 4
  • 26
  • 29