because of this: #listfirst(p.mls_number, '_')# you can't use a cf function on a query column inside same query. it thinks you are referring to a cf structure p with member mls_number
look into using mysql string functions instead. you are also missing a ) in the query... Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Rick Faircloth wrote: > Hi, all.. > > > > Here's the query: > > select p.property_id, p.mls, p.street_number, p.street_name, p.city, > p.remarks, p.list_price, > p.property_type, > p.mls_number, o.mls, o.office_id, o.office_name, o.display_order, > (select hp.photo_filename from hmls_photos hp, properties p where > hp.photo_mls_number = > #listfirst(p.mls_number, '_')# limit 1 as > prop_photo_filename > from properties p, offices o > where p.listing_office_mls_id = o.mls_office_id > and p.mls = o.mls > order by o.display_order > > > > Here's the error: > > Element MLS_NUMBER is undefined in P. > > The error occurred in > E:\Inetpub\webroot\c21-ar\includes\include_browse_query_01.cfm: line 18 > > 16 : select p.property_id, p.mls, p.street_number, p.street_name, p.city, > p.remarks, > p.list_price, p.property_type, > 17 : p.mls_number, o.mls, o.office_id, o.office_name, o.display_order, > 18 : (select hp.photo_filename from hmls_photos hp, properties p > where > hp.photo_mls_number = #listfirst(p.mls_number, '_')# limit 1 > as prop_photo_filename > 19 : from properties p, offices o > 20 : where p.listing_office_mls_id = o.mls_office_id > > I can't see anything wrong with the query. > > Ideas? > > Rick > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:305749 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
