我在录取此SQL命令时得到以下异常:
SELECT (ACOS(SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)+COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)*COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0))*6371) AS foo
system.data.entity.core.entityCommandExecutionException 发生了无效的浮点操作"
我必须如何更改查询才能运行?我的方法是
ROUND( statement, 2)
但是它不起作用。
这是经度和纬度的位置搜索
更新:无法与SQL Server 2017一起使用http://sqlfiddle.com/#!18/9eecb/38598
ACOS()
中的语句圆形
SELECT (ACOS( ROUND(SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)+COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)*COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0), 15))*6371) AS foo
这是因为SQL中的浮动对话。如果不舍入,SQL Server假设输入值略大于1,则可以绕输入,或者在将其传递给acos()
此查询的结果是 6371
:
SELECT (
SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)+COS(PI()
*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)*COS(PI()
*13.7910680057092/180.0-PI()*13.7910680057092/180.0)
)*6371
AS foo
但是,6371
不符合ACOS
的资格。ACOS
应该较低或等于1
。
例如:
SELECT ACOS(1) -- OK
SELECT ACOS(1.1) -- An invalid floating point operation occurred.
更新:
此查询可以返回大于1的值。因此,尝试使用CASE
语句:
SELECT
(
ACOS
(
CASE WHEN
(SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)
+COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)
*COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0)) > 1
THEN 1
ELSE
(SIN(PI()*51.0026829600855/180.0)*SIN(PI()*51.0026829600855/180.0)
+COS(PI()*51.0026829600855/180.0)*COS(PI()*51.0026829600855/180.0)
*COS(PI()*13.7910680057092/180.0-PI()*13.7910680057092/180.0))
END
)
*6371) AS foo