Used QuotedValueList along with PreserveSingleQuotes... and it worked!
Thanks all!
<cfset manager_direct_staff =
quotedValuelist(get_manager_staff.userid)>
<cfquery name="get_category_avg" datasource="byofocal">
SELECT AVG(dbo.answer_rating.answer_value) as category_avg
FROM dbo.answer_rating
WHERE (dbo.answer_rating.site_id = #ATTRIBUTES.site_id#) AND
(dbo.answer_rating.question_id IN
(#category_qid#)) AND
(dbo.answer_rating.eval_email in
(#preservesingleQuotes(manager_direct_staff)#)) AND
(dbo.answer_rating.self = 'n')
</cfquery>
At 07:27 PM 7/26/2002 -0400, Jon Hall wrote:
>manager_direct_staff is just a list of the records that come back in
>the get_manager_staff.userid query column right? So get rid of that
>loop, and just change the second query to this:
>
><cfquery name="get_category_avg" datasource="emp_review">
>SELECT AVG(dbo.answer_rating.answer_value) as category_avg
>FROM dbo.answer_rating
>WHERE (dbo.answer_rating.site_id = #ATTRIBUTES.site_id#)
>AND (dbo.answer_rating.question_id IN (#category_qid#))
>AND (dbo.answer_rating.eval_email IN
>(#quotedValueList(#get_manager_staff.userid#))
>AND (dbo.answer_rating.self = 'n')
></cfquery>
>
>The quotedValueList() function was created exactly for what you are
>doing, I imagine the format of the list is what is causing your
>problems. If this doesn't work, can you send the error you are
>getting?
>
>--
>Jon mailto:[EMAIL PROTECTED]
>
>Friday, July 26, 2002, 7:05:28 PM, you wrote:
>MEC> I'm having difficulty using the "IN" operator condition on a text list
>MEC> dynamically generated from previous query. Any suggestions?
>
>
>MEC> <Cfquery name="get_manager_staff" datasource="emp_records">
>MEC> SELECT userid
>MEC> FROM emp
>MEC> WHERE status='A'
>MEC> AND level = 2
>MEC> start with userid='#ATTRIBUTES.manager#'
>MEC> connect by sup_emp_no=prior emp_no
>MEC> and emp_no <> sup_emp_no
>MEC> </CFQUERY>
>
>MEC> <cfparam name="get_manager_staff" default="">
>MEC> <cfparam name="name_here" default="">
>MEC> <cfloop query="get_manager_staff">
>MEC> <cfset name_here = "'" & get_manager_staff.userid & "'">
>MEC> <cfset manager_direct_staff =
>MEC> listappend(manager_direct_staff,name_here)>
>MEC> </cfloop>
>
>
>
>MEC> <cfquery name="get_category_avg" datasource="emp_review">
>MEC> SELECT AVG(dbo.answer_rating.answer_value) as
>category_avg
>MEC> FROM dbo.answer_rating
>MEC> WHERE (dbo.answer_rating.site_id =
>#ATTRIBUTES.site_id#) AND
>MEC> (dbo.answer_rating.question_id IN
>MEC> (#category_qid#)) AND
>MEC> (dbo.answer_rating.eval_email IN
>MEC> (#manager_direct_staff#)) AND
>MEC> (dbo.answer_rating.self = 'n')
>MEC> </cfquery>
>
>
>MEC> In this case, I am having some problems using the "manager_direct_staff"
>MEC> list variable.
>
>MEC> Thanks SQL experts!
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
