Binary Vision blog
ColdFusion valueList() operator in SQL statements
Posted 14 Dec 2011 by Peter B
There's been a few occasions I have used this to good effect, but find myself re-investigating it each time I go to do a similar task, so I thought it was about time I wrote something up on it.
The scenario - In 'Table1' I have a column called 'ID', and another column called 'anotherID' in this case, both of which are char(35) and contain a UUID string. 'anotherID' relates to a column called 'ID' in another table, 'Table2'. What I want to do is select from 'Table2' using the SQL 'IN' operator, and use the contents of the column 'anotherID' from Table1 as the list of values.
Enough long-winded explanation, here's the code:
select ID, anotherID from Table1
</cfquery>
<cfquery name = "getAgain" datasource="DSN">
select * from Table2 where ID in ('#ValueList(get.anotherID,"','")#')
</cfquery>
The cunning bit here is the use of the optional 'delimiter' argument in the valueList() function, this allows me to specify the delimiter as ',' -which is what we need for the SQL statement to succeed. wrap it up in brackets and one more set of single quotes and it is valid SQL.
Now I know you are going to ask about just doing all that in SQL with a join.... WELL, I do a fair bit of cross-datasource transactions, and often post-process the results of the first query, and for my situation valueList() is a good solution.