I have a sort function on a search results page that is giving me an Ambiguous
column name error, and for the life of me I do not know why. I can sort by
price, SKUName and photo just fine but cannot sort by city or state. I have a
city and state column in the table I am joining with, but cannot see that would
be the problem. Am I wrong?
Query
SELECT #APPLICATION.DBPRE#MemberProfile.RscreenName,
#APPLICATION.DBPRE#Advertisements.AdID,
#APPLICATION.DBPRE#Advertisements.MemberID,
#APPLICATION.DBPRE#Advertisements.CategoryID,
#APPLICATION.DBPRE#Advertisements.SCategoryID,
#APPLICATION.DBPRE#Advertisements.ItemPhoto,
#APPLICATION.DBPRE#Advertisements.SKUName,
#APPLICATION.DBPRE#Advertisements.Price,
#APPLICATION.DBPRE#Advertisements.AQuantity,
#APPLICATION.DBPRE#Advertisements.State,
#APPLICATION.DBPRE#Advertisements.County,
#APPLICATION.DBPRE#Advertisements.City
FROM #APPLICATION.DBPRE#Advertisements
INNER JOIN #APPLICATION.DBPRE#MemberProfile
ON #APPLICATION.DBPRE#Advertisements.MemberID =
#APPLICATION.DBPRE#MemberProfile.MemberID
WHERE #APPLICATION.DBPRE#Advertisements.Status = <cfqueryparam
cfsqltype="CF_SQL_BIT" value="#Val(ARGUMENTS.Status)#">
offending code
<cfif Len(ARGUMENTS.search_visitor_city) gt 0>
AND #APPLICATION.DBPRE#Advertisements.State = <cfqueryparam
cfsqltype="CF_SQL_CHAR" value="'#Trim(ARGUMENTS.search_state)#'">
AND #APPLICATION.DBPRE#Advertisements.City = <cfqueryparam
cfsqltype="CF_SQL_VARCHAR" value="'#Trim(ARGUMENTS.search_visitor_city)#'">
</cfif>
ORDER BY #ARGUMENTS.sortBy#
#ARGUMENTS.sortDirection#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261351
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
