定义变量，无效标识符.欧氏距离

Question

oracle一直拒绝任何我尝试在with子句中调用经度的东西。

 WITH params as (
      SELECT -28 as lat, 151 as lon
      FROM dual
     )
SELECT sighting_id 
FROM sightings 
ORDER BY sqrt(power(lat - latitude, 2) + power(lon - longitude, 2));

Answer 1

您在WITH中定义了参数，但从不在FROM中引用它。

WITH params as (
      SELECT -28 as lat, 151 as lon
      FROM dual
     )
SELECT sighting_id 
FROM sightings CROSS JOIN params 
ORDER BY sqrt(power(lat - latitude, 2) + power(lon - longitude, 2));