One Last Hurdle >> SQL ALIAS

# Thread: One Last Hurdle >> SQL ALIAS

1. Senior Member
Join Date
Dec 1969
Posts
661

## One Last Hurdle >> SQL ALIAS

Â*<BR>Hi,<BR><BR>This Question relates to a problem I was trying to solve yesterday. <BR><BR>see: http://www.aspmessageboard.com/forum/asp.asp?M=699783&T=699763&F=20&P=1<BR><BR>It has to do with sorting records by the distance between locations via a calculation. This calculation came after the SQL so I was unable to sort with out making the dreaded Bubble Sort array. Bill came up with an excellent idea which was to use the pythagorean forumula: hypotenuse^2 = side1^2 + side2^2 to get the degree.<BR><BR>This looks like this in the SQL. The calculation becomes an alias.<BR><BR>[code language="language"]<BR>"SELECT ((" & lat1 & " - zipnov99.LATITUDE)*(" & lat1 & " - zipnov99.LATITUDE)) + ((" & long1 & " - zipnov99.LONGITUDE)*(" & long1 & " - zipnov99.LONGITUDE)) AS RawOrder, zipnov99.LATITUDE, zipnov99.LONGITUDE, ...blah ... blah ... ;"<BR>[/code]<BR><BR>This alias can be then response.write and works.<BR><BR>However, all I want to do is ORDER BY this. This gives the error.<BR><BR>[code language="language"]<BR>"SELECT ((" & lat1 & " - zipnov99.LATITUDE)*(" & lat1 & " - zipnov99.LATITUDE)) + ((" & long1 & " - zipnov99.LONGITUDE)*(" & long1 & " - zipnov99.LONGITUDE)) AS RawOrder, zipnov99.LATITUDE, zipnov99.LONGITUDE, ...blah ... blah ... ORDER BY RawOrder ;"<BR>[/code]<BR><BR>Microsoft JET Database Engine (0x80040E10)<BR>No value given for one or more required parameters.<BR><BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## I'm almost sure...

...that I mentioned this earlier. But maybe it was in a different thread.<BR><BR>Access SQL *does not allow* you to use an aliased name in the ORDER BY.<BR><BR>You *must* copy the *ENTIRE* expression to the ORDER BY.<BR><BR>Period.<BR><BR>And that having been said, I will ask you why you want to SELECT this peculiar value. You&#039;re going to recalculate the actual distance after the query is made, anyway, yes? <BR><BR>So just move the expression out of the SELECT and only into the ORDER BY.<BR><BR>SQL = "SELECT zipnov99.LATITUDE, zipnov99.LONGITUDE, "<BR> & " ...blah ... blah ... " _<BR> & " ORDER BY ((" & lat1 & " - zipnov99.LATITUDE)*(" & lat1 & " - zipnov99.LATITUDE)) + ((" & long1 & " - zipnov99.LONGITUDE)*(" & long1 & " - zipnov99.LONGITUDE))"<BR><BR>By the way, you could simplify that a tad:<BR><BR>SQL = "SELECT zipnov99.LATITUDE, zipnov99.LONGITUDE, "<BR> & " ...blah ... blah ... " _<BR> & " ORDER BY (" & lat1 & " - zipnov99.LATITUDE)^2 * (" & long1 & " - zipnov99.LONGITUDE)^2"<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## Sorry!

Left in wrong operator in simplified form.<BR><BR>SQL = "SELECT zipnov99.LATITUDE, zipnov99.LONGITUDE, "<BR> & " ...blah ... blah ... " _<BR> & " ORDER BY (" & lat1 & " - zipnov99.LATITUDE)^2 + (" & long1 & " - zipnov99.LONGITUDE)^2"<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
661

## Nothing to be sorry about..

This is it. Problem solved with an absolutely simple yet fantastic solution.<BR><BR>Bill, thanks a million. As always you go the extra mile.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•