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
