How to make this query easier?


2005-04-06 09:15:35 PM
delphi211
Hi,
I'm using an UDF CalcDistance to calculate the distance between two points
based on their latitude and longitude.
I the resultset to contain only give me the nearest one(s).
select
Customer.IDCode, Customer.IDFullName,
Employee.EmpCode, Employee.EmpFullName,
CalcDistance(IDLatitude, IDLongitude, EmpLatitude, EmpLongitude)
from Customer, Employee, Employee_Transport
where (Customer.IDCode = 204)
and (Employee.Emptransport = Employee_Transport.TransportCode)
and (CalcDistance(IDLatitude, IDLongitude, EmpLatitude, EmpLongitude) =
(select Min(CalcDistance(IDLatitude, IDLongitude, EmpLatitude,
EmpLongitude))
from Customer, Employee, Employee_Transport
where (Customer.IDCode = 204)
and (Employee.Emptransport = Employee_Transport.TransportCode)
and (CalcDistance(IDLatitude, IDLongitude, EmpLatitude, EmpLongitude) <
Employee_Transport.TransportRange)))
Is there a way i can achieve the same without rewriting half of the query
twice?
Regards,
Olivier Peter