IN requires parentheses to delimit the expression it is searching within: .... NOT IN (#listOfIds#) ...
And use CFQUERYPARAM. Really. cheers, barneyb On Mon, Apr 6, 2009 at 11:02 AM, Dave Long <[email protected]> wrote: > > I'm trying to create a page with two lists consisting of data from two > different sources. The first list is a table of real estate property > listings that have been processed and the second is a list of properties > *remaining* to be processed. > > First, I obtained the processed listings: > > > <CFQUERY name="GetRecord" dataSource="#DSN2"> > SELECT MLS_number > FROM VPT > ORDER BY MLS_number > </CFQUERY> > > > Next I created a list from the first query: > > > <CFOUTPUT query="GetRecord"> > <CFSET list = "#GetRecord.MLS_number#"> > </CFOUTPUT> > > > To verify that the list was actually created, I ouptut the list to the > screen with > > #list# > > Which produced this correct list of processed numbers: > > > 87867 88623 89035 89094 89256 100336 100349 100506 100516 100519 100520 > 100521 100522 100708 100711 100713 100716 100719 100803 100804 100811 100815 > 100817 100868 101042 > > > > 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> > > > This produces the following error: > > ODBC Error Code = 37000 (Syntax error or access violation) > > > > [MERANT][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near > '101042'. > > > > SQL = "SELECT MLS_Number, List_Price FROM Residential WHERE List_Firm = 175 > AND MLS_Number NOT IN 101042 ORDER BY MLS_Number" > > First of all, I'm puzzled that it displays the last MLS number instread of > th first. Secondly, I suspect I need to have the list separated by commas > but it throws another syntax error if I include them in the CFSET tag. The > results do not change whether I use single or double quotes or no quotes at > all. > > So far, I am unable to find any other syntax for comparing the second query > results to the list. Can anyone advise me? > > Dave Long > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:321350 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
