May 8th, 2008, 11:31 AM
|
|
Contributing User
|
|
Join Date: Apr 2001
Location: knee deep in sh*t
|
|
|
Procedure w/ function returning "double" results
Hi All,
I have a stored procedure:
Code:
CREATE PROCEDURE GetNearbyZipCodes_better(
zipbase varchar(6)
, range numeric(15)
)
BEGIN
DECLARE lat1 decimal(5,2);
DECLARE long1 decimal(5,2);
DECLARE rangeFactor decimal(7,6);
SET rangeFactor = 0.014457;
SELECT
latitude
, longitude into lat1
, long1
FROM zipcodes
WHERE
zip = zipbase;
SELECT DISTINCT
B.zip
, GetDistance(lat1, long1, B.latitude, B.longitude) distance
FROM zipcodes AS B
WHERE
B.latitude BETWEEN lat1-(range*rangeFactor) AND lat1+(range*rangeFactor)
AND B.longitude BETWEEN long1-(range*rangeFactor) AND long1+(range*rangeFactor)
AND GetDistance(lat1, long1, B.latitude, B.longitude) <= range;
END
that calls a method named GetDistance. When the GetDistance call was only in the WHERE clause and the zip was being returned, the results from the query were just a single listing of zip codes.
Now I have added a call to the GetDistance function to the SELECT so that it can be returned also but the result set that is being returned includes "double" results: so the same zip and distance are returned twice.
So if the result should be 10 results, it will be 20, just the first 10 repeat again. It seems that since there is two calls to the function it is returning the results twice and I don't get it?? 
Any ideas on how to fix this?
Thank you for your time,
oach
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
