Big Lists in Queries with CF 4.5.2

[Guest_imported]

Hi all,
I'm trying to run a cfquery and verify one of my results is in a set, like so:

and force.uln_id IN ('#ulnset#')

The problem is ulnset can have more than 1000 items in it, and SQL doesn't like that. And CF 4.5.2 can't do queries w/in queries. Besides running through the list using a loop, is there an easy way to do this?

 

[Silvano]

correction: coldfusion support subqueries and you can nest them many levels deep; it can be nested inside of a select, update, insert, or delete query in either select, from or where clause; if the subquery returns more than one result, it can be used in the where clause Sylvano
dsylvano@hotmail.com

"every and each day when I learn something new is a small victory..."

 

[tleish]

I'm not exactly sure what you are asking. Are you wanting to shorten the list, or are you simply wanting to Qualify the list with single quotes around each item. If you want to shorten the list, there are several CF List Fuctions that can do this.

However, if you want to qualify the list then the the ColdFusion function you are looking for is:

ListQualify
Returns a list with a qualifying character around each item in the list, such as double or single quotes.

If:

ulnset = "New York,Paris,3"

WHERE City IN (#ListQualify(ulnset, "'&quot#)

would be:

WHERE City IN ('New York','Paris','3')


ListQualify has some other great parameters, like you can specify which elements to Qualify. In this case, we only want to qualify characters, and not quote numbers.

If:

ulnset = "New York,Paris,3"

WHERE City IN (#ListQualify(ulnset, "'",",","CHAR&quot#)

would be:

WHERE City IN ('New York','Paris',3) - tleish