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
-----Original Message-----
From: Dave Long [mailto:[email protected]]
Sent: Monday, April 06, 2009 2:03 PM
To: cf-talk
Subject: 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.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:321354
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
