HELP with WHERE IN (LIST) syntax

[capitano]

I'm trying to do the following:

...prior query name='earlier_query'

...

<cfset mylist=QuotedValueList(earlier_query.column)>

<cfquery datasource=&quot;something&quot; name=&quot;new_query&quot;>
SELECT column1, column2
FROM table
WHERE column2 in ('#mylist#')
</cfquery>

<cfoutput>
RecordCount: #new_query.RecordCount#
</cfoutput>

However, I'm not getting any results because the ColdFusion server is interpreting ('#mylist#') as a single element in the list:
(''item1', 'item2', 'item3'')

where the single item is: &quot; 'item1','item2','item3' &quot;

So, if I try to change the line to:
WHERE column2 IN (#mylist#)

then I get errors: &quot;Syntax error (missing operator) in query expression &quot;

So how do I escape these *!@#$%!* quotes?
Sorry for the slang, but I'm really getting angry.

Thanks for any help!

 

[tlhawkins]

Hey capitano,

I usually do a loop for these but another thing to try the way you are doing it is like this:

<cfset mylist=QuotedValueList(earlier_query.column)>
<cfset mylist = &quot;( #mylist# )&quot;
<cfquery datasource=&quot;something&quot; name=&quot;new_query&quot;>
SELECT column1, column2
FROM table
WHERE column2 in #mylist#
</cfquery>

<cfoutput>
RecordCount: #new_query.RecordCount#
</cfoutput>

however you try it always output #mylist# so you can see exactly what you're sending to the Query, sometimes there is an obvious mistake if you look at it.

These In (...) lists drive me crazy. If this way doesn't work you may want to try making a Cfloop that does it from the Query.

just some ideas. I hope it works for you.

 

[Sarky78]

Try using this syntax instead of what you have got this usually works for me :

<cfquery datasource=&quot;something&quot; name=&quot;new_query&quot;>
SELECT column1, column2
FROM table
WHERE column2 in (`#ValueList(earlierQuery.column)#')</cfquery>

Hope this helps !

 

[Guest_imported]

Use PreserveSingleQuotes():

WHERE column2 in (#PreserveSingleQuotes(mylist)#)