SQL Query to Order by distance - my brain is fried

D

DigitalFusion

Hey all,

I have the following query on my ASP page that is giving me a
"syntax error in JOIN opperation" message. I found this query from
someone who posted it in PHP format, so I had to convert it over to
ASP, and change the table names for my database:

SQL1 = "SELECT * From US_ZIP where ZIP= '" &
Request.QueryString("Search") & "'"
Set objRS1 = Connection.Execute(SQL1)
iStartLat = objRS1("Latitude")
iStartLong = objRS1("Longitude")
iRadius = Request.querystring("distance")
LatRange = iradius / ((6076 / 5280) * 60)
LongRange = iRadius / (((cos(cdbl(iStartLat * 3.141592653589 / 180))
* 6076.) / 5280.) * 60)
LowLatitude = istartlat - LatRange
HighLatitude = istartlat + LatRange
LowLongitude = istartlong - LongRange
HighLongitude = istartlong + LongRange

SQL_List = "SELECT Dealers.*, US_ZIP.ZIP, US_ZIP.Latitude,
US_ZIP.Longitude"_
&"(69.09*DEGREES(ACOS(SIN(RADIANS(US_ZIP.latitude))"_
&"*SIN(RADIANS("&istartlat&"))+COS(RADIANS(US_ZIP.latitude))"_

&"*COS(RADIANS("&istartlat&"))*COS(RADIANS(US_ZIP.longitude-"&istartlong&")))))"_
&" AS distance "_
&"FROM Dealers, US_ZIP "_
&"INNER JOIN US_ZIP ON Dealers.DealerZIP = US_ZIP.ZIP "_
&"WHERE US_ZIP.latitude <= "&highLatitude&" "_
&"AND US_ZIP.latitude >= "&lowLatitude&" "_
&"AND US_ZIP.longitude >= "&lowLongitude&" "_
&"AND US_ZIP.longitude <= "&highLongitude&" "_
&"ORDER BY distance"

response.write SQL_List
response.end

can anyone help me figure out where I have went wrong?
 
D

DigitalFusion

Hey all,

I have the following query on my ASP page that is giving me a
"syntax error in JOIN opperation" message. I found this query from
someone who posted it in PHP format, so I had to convert it over to
ASP, and change the table names for my database:

SQL1 = "SELECT * From US_ZIP where ZIP= '" &
Request.QueryString("Search") & "'"
Set objRS1 = Connection.Execute(SQL1)
iStartLat = objRS1("Latitude")
iStartLong = objRS1("Longitude")
iRadius = Request.querystring("distance")
LatRange = iradius / ((6076 / 5280) * 60)
LongRange = iRadius / (((cos(cdbl(iStartLat * 3.141592653589 / 180))
* 6076.) / 5280.) * 60)
LowLatitude = istartlat - LatRange
HighLatitude = istartlat + LatRange
LowLongitude = istartlong - LongRange
HighLongitude = istartlong + LongRange

SQL_List = "SELECT Dealers.*, US_ZIP.ZIP, US_ZIP.Latitude,
US_ZIP.Longitude"_
&"(69.09*DEGREES(ACOS(SIN(RADIANS(US_ZIP.latitude))"_
&"*SIN(RADIANS("&istartlat&"))+COS(RADIANS(US_ZIP.latitude))"_

&"*COS(RADIANS("&istartlat&"))*COS(RADIANS(US_ZIP.longitude-"&istartlong&")))))"_
&" AS distance "_
&"FROM Dealers, US_ZIP "_
&"INNER JOIN US_ZIP ON Dealers.DealerZIP = US_ZIP.ZIP "_
&"WHERE US_ZIP.latitude <= "&highLatitude&" "_
&"AND US_ZIP.latitude >= "&lowLatitude&" "_
&"AND US_ZIP.longitude >= "&lowLongitude&" "_
&"AND US_ZIP.longitude <= "&highLongitude&" "_
&"ORDER BY distance"

response.write SQL_List
response.end

can anyone help me figure out where I have went wrong?
Click to expand...

BTW - I removed the WHERE clause and ran it like this:

SELECT Dealers.*, US_ZIP.*,
(69.09*DEGREES(ACOS(SIN(RADIANS(US_ZIP.latitude))*SIN(RADIANS(45.412894))
+COS(RADIANS(US_ZIP.latitude))*COS(RADIANS(45.412894))*COS(RADIANS(US_ZIP.longitude--92.633615)))))
AS distance FROM Dealers, US_ZIP

and I got a better message: "undefined function DEGREES in expression"

obviously it has something to do with teh formula, but since I cant do
trig for the life of me, I cant really read that formula
 
Top