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

djw said on Jan 5, 2005 at 6:48 PM :

I'm finding an odd ValueList change between CF5 and CFMX 6.1

The function turns integer values into floating points (this is the same if the query item is text with a number or numeric!).

Try the following code in CF5 and CFMX to see the change:

<cfscript>
qTest = QueryNew("idnum,idtext");

for(i=1; i LTE 5; i=i+1) {
newRow = QueryAddRow(qTest);
tmp = QuerySetCell(qTest,"idnum", i);
tmp = QuerySetCell(qTest,"idtext", "#i#");
}
</cfscript>

<cfdump var="#qTest#">

<cfoutput>
#ValueList(qTest.idnum)# <br />
#ValueList(qTest.idtext)#
</cfoutput>

Is this by design?

cheers

David

MikeG said on Jan 17, 2005 at 4:36 PM :

We found this behavior as well, but offer 2 workarounds. We use an oracle DB environment and found that if you format your number column like this

MyNumberColumn NUMBER(12,0)

Instead of this

MyNumberColumn NUMBER

The valuelist works as expected and returns integers.

And for those of you where changing the database is not an option...

<cfscript>
function FixMXValueList(mylist) {
if(arraylen(arguments) gt 1) {mydelim=arguments[2];}
else {mydelim=",";}
arr=listToArray(mylist,mydelim);
newlist="";
for(i=1;i lte arraylen(arr);i=i+1) {
newlist=listappend(newlist,int(arr[i]),mydelim);
}
return newlist;
}
</cfscript>

galadave said on Jul 5, 2005 at 4:15 PM :

I encoutered this problem as with Oracle, using native drivers, during an MX upgrade as well. What's interesting is that on one of our development boxes, and on another we don't. We can't pinpoint exactly where the differences lies, but Im guessing that it lies in a subtle difference in the Oracle driver between the two machines. Obviously this can be coded around, but I would love to hear from someone who had the same problem so we can compare notes and perhaps pinpoint the problem.

ColdSteel2 said on Apr 4, 2006 at 1:00 PM :

I Found that changing the select statemtment bu putting hte number filed int a to_char(Number_field) also works.