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/
> -----Original Message-----
> From: Barney Boisvert [mailto:[email protected]]
> Sent: Monday, April 06, 2009 1:09 PM
> To: cf-talk
> Subject: Re: Finding values not in a list
>
>
>
> 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:321355
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
