ValueList

ValueList(query.column [, delimiter ])

<h3>ValueList Example</h3>

<!--- use the contents of a query to create another dynamically --->
<cfquery name = "GetDepartments" datasource = "cfsnippets">
   SELECT Dept_ID FROM Departments
   WHERE Dept_ID IN (`BIOL')
</cfquery>

<cfquery name = "GetCourseList" datasource = "cfsnippets">
SELECT *
   FROM CourseList
   WHERE Dept_ID IN (`#ValueList(GetDepartments.Dept_ID)#')
</cfquery>

<cfoutput QUERY = "GetCourseList" >
<pre>#Course_ID#   #Dept_ID#   #CorNumber#   #CorName#</pre>
</cfoutput>

Comments

No screen name said on Mar 18, 2004 at 2:58 AM :

28 : SELECT ItemName, ItemID, ITemPrice FROM Items
29 : where Category_ID=11
30 : and ItemID in ('#ValueList(system_detail.cpu)#')
31 : ORDER by ItemPrice ASC
32 : </cfquery>

('#ValueList(system_detail.cpu)#') gave me an error but if i remove the ' ' like this
(#ValueList(system_detail.cpu)#) the error is gone...

SELECT ItemName, ItemID, ITemPrice FROM Items where Category_ID=11 and ItemID in (58,15,59,16,60,61,17,62) ORDER by ItemPrice ASC

only return the first match where itemID = 58, but not rest of them...
is there a way to get all the itemID thats in the list?

tzimmerman said on Apr 27, 2004 at 3:12 PM :

The following shows a better example of how this function works:

<!--- use the contents of a query to create another dynamically --->
<cfquery name = "GetDepartments" datasource = "cfsnippets">
SELECT Dept_ID FROM Departments
WHERE Dept_ID IN ('BIOL')
</cfquery>

<cfquery name = "GetCourseList" datasource = "cfsnippets">
SELECT *
FROM CourseList
WHERE Dept_ID IN ('#GetDepartments.Dept_ID#')
</cfquery>

<br>

Value list of all BIOL Course ID's using (--) as the delimiter:<br>
<cfoutput>
#ValueList(GetCourseList.Course_ID,"--")#<br>
</cfoutput>

Value list of all BIOL Course Numbers using (;) as the delimiter:<br>
<cfoutput>
#ValueList(GetCourseList.CorNumber,";")#<br>
</cfoutput>

NAGUIT_ALA said on Jan 28, 2005 at 3:39 PM :

problem found when i use ValueList(query.smallIntcolumn), it adds a decimal point and a zero. i.e. 3123 & 3124 becomes 3123.0,3134.0

CFSMITA said on Apr 15, 2005 at 11:35 AM :

I too have the same problem, ValueList of any integer column is converted to list of real values with .0

jrunrandy said on Apr 25, 2005 at 1:28 PM :

The .0 problem is bug 53488, which is fixed in ColdFusion MX 7.

CGountanis said on Jul 11, 2006 at 7:05 AM :

I am still having that issue with 7.0.1 and Hotfixes up-to-date as of 7/11/2006. Doing a valuelist though evaluiate still adds decimals.

No screen name said on Sep 18, 2006 at 8:08 AM :

using ColdFusion Server Developer 7,0,2,142559 and still the same problem

halL said on Sep 19, 2006 at 6:56 AM :

Please note that this page is for ColdFusion MX 6.1.

The problem listed in bug 53488 is fixed using the DataDirect drivers version 3.3 build 45 and higher.
You can download the DataDirect version 3.5 drivers from the following URL:
http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=42dcb10a

Also, if this response does not resolve the issue, please note that Livedocs is not a forum for technical support, and we cannot address it further here.
You can address support issues in the online web forums at http://www.adobe.com/cfusion/webforums.