Thanks everyone, but I must be trying to accomplish the impossible with our version 5 CF server. None of these suggestions has worked. I guess we're just going to have to write down which listings we've processed.
Thanks again. > -----Original Message----- > From: Yuliang Ruan [mailto:[email protected]] > Sent: Monday, April 06, 2009 2:53 PM > To: cf-talk > Subject: Re: Finding values not in a list > > > > > > >Next I created a list from the first query: > > > > > ><CFOUTPUT query="GetRecord"> > ><CFSET list = "#GetRecord.MLS_number#"> > ></CFOUTPUT> > > > > This code does NOT produce a list. this only sets the list > variable to the current record's MLS_number. the correct > code to create a list of all the MLS_numbers in the current > recordset would be: > > <cfloop query="GetRecord"> > <cfset list=listappend(list,getrecord.MLS_number)> > </cfloop> > > or equiv: > > <cfset list=ValueList(GetRecord.MLS_number)> > > >Then I tried to create a list of unprocessed listings from > the other DB > >by comparing the MLS_Number to the list: > > > > > ><CFQUERY name="GetResidential" dataSource="#DSN1#"> > > SELECT MLS_Number > >FROM Residential > >WHERE List_Firm = #FirmID# AND MLS_Number NOT IN #list# > >ORDER BY MLS_Number > > </CFQUERY> > > > > <CFQUERY name="GetResidential" dataSource="#DSN1#"> > SELECT MLS_Number > FROM Residential > WHERE List_Firm = <cfqueryparam > cfsqltype="CF_SQL_INTEGER" value="#FirmID#"> AND MLS_Number > NOT IN (<cfqueryparam cfsqltype="CF_SQL_CHAR" value="#list#">) > ORDER BY MLS_Number > </CFQUERY> > > > note the parens around the list. also swapped you over to > cfqueryparam syntax > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321367 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
