Finding values not in a list

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.

>Next I created a list from the first query: ----- Excess quoted text cut - see Original Post for more ----- 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)> ----- Excess quoted text cut - see Original Post for more ----- <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

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. ----- Excess quoted text cut - see Original Post for more -----

IN requires parentheses to delimit the expression it is searching within: .... NOT IN (#listOfIds#) ... And use CFQUERYPARAM.  Really. cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

Well, that helped... I think. I added a comma to the CFSET that creates the list: <CFOUTPUT query="GetRecord"> <cfset list = "#ViewField2#,"> #list# </CFOUTPUT> Which displays: 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, And here's the comparison query now: <CFQUERY name="GetResidential" dataSource="#DSN#"> SELECT MLS_Number FROM Residential WHERE List_Firm = #FirmID# AND MLS_Number NOT IN <CFQUERYPARAM VALUE=(list) CFSQLTYPE="CF_SQL_INTEGER" SEPARATOR=","> ORDER BY MLS_Number   </CFQUERY> But now I get this error: Error Diagnostic Information VALUE Invalid data '(list)' for CFSQLTYPE 'CF_SQL_INTEGER'. Once again, I tried adding single and doublew quotes as well as # # to the (list) in the query but still get errors. What am I overlooking/ ----- Excess quoted text cut - see Original Post for more -----

Also use valuelist which means you don't need to create a list first. <CFQUERY name="GetResidential" dataSource="#DSN1#">     SELECT MLS_Number     FROM Residential     WHERE List_Firm = #FirmID#     AND MLS_Number NOT IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#ValueList(GetRecord.MLS_number)#" list="yes">)     ORDER BY MLS_Number </CFQUERY> -- Casey

Ummm...try this: <cfquery name="getRecord" datasource="#dsn2#"> <-- second # was missing in first query      select     mls_number      from       VPT      where      mls_number      not in     (select     mls_number                  from       residential                  where      list_firm = #firmid#) </cfquery> And I realized that you're apparently not using MySQL, so this syntax may not work for you. There is another way to do this, if this doesn't work. (and don't forget cfqueryparam for #firmID#) Rick 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.

> So far, I am unable to find any other syntax for comparing the second query > results to the list. Can anyone advise me? look into the valueList() function for getting a comma-delimited list of values from a query column. Your IN values need to be enclosed in parentheses:      AND MLS_Number NOT IN (#valueList(GetRecord.MLS_number)#) ... and you'll also want to cfqueryparam that: AND MLS_Number NOT IN (<cfqueryparam value="#valueList(GetRecord.MLS_number)#" cfsqltype="cf_sql_integer" list="true" />) -- I have failed as much as I have succeeded. But I love my life. I love my wife. And I wish you my kind of success.

Charlie! You did it!!! Dahnke! ¡Gracias! Thank you! Thank you! Thank you!!! It's taken me all day to get this right. Thank goodness, I'm self-employed or I'd be looking for a new place to sit down to work. Dave ----- Excess quoted text cut - see Original Post for more -----