Using a List within a Where Clause

[Suva1]

I'm trying to use a dynamic list within a where clause, not having much luck and have tried many different ways.

Here is the closest thing I can come up with, the only problem is that the output returns an error due to commas in the where criteria if the list contains more than one item.

Any help would be greatly appreciated!

Select is the name of my comma delimited list.

<cfquery name="list" datasource="#dbnm#">
SELECT *
FROM students
where status = '#x#' and (id = #Listqualify(select,"")#)
</cfquery>

[Dan_Bracuk · Accepted solution]

When using lists in sql, you don't use an equal sign. You use the keyword in. You also have to put your list in parentheses.

[Suva1]

Thank you, unfortunately the dynamic data consists of integers, so if one id was 100 and another was 1000 and my select criteria was say 100, then the data for both ID 100 and ID 1000 would be displayed which would be incorrect? Since they are integers I believe that quotes would cause a syntax error.

I did try as you suggested but still receive errors, perhaps you could supply a modified example of my code?

Perhaps I can take some text data and tie it together to create a unique string and use an in statement?

Any other ideas?

[Suva1]

OK, I believe I understand the IN clause now, we are looking for items where ID is in the LIST named select.

I am still unable to get it to work and have tried various configurations.

I'm receiving the following syntax error with or without quotes:

[Microsoft][ODBC Microsoft Access Driver] In operator without () in query expression 'status = 'Active' and (id in [234,242])'.

[Dan_Bracuk]

http://dictionary.reference.com/search?q=parentheses

[Suva1]

That's really funny, thanks for the expert help. Always good to make someone feel welcome and comfortable asking questions when they have a problem.

[Suva1]

Perhaps someone else a bit less lofty and more inclined to assist rather than humiliate would be willing to assist.

Here is the bit of code as it stands:

<cfquery name="list" datasource="#dsnm#">
SELECT *
FROM students
where status = '#x#' and id in "#listqualify(select,"")#"
order by #sort_order# asc
</cfquery>

As stated above I've tried a number of different variations, leaving out listqualify, with and w/o double quotes, with and w/o single quotes, with and w/o parenthesis, etc., as well as using text data instead of integers; I've even pruned out the criteria for the leading AND statement. The code above generates the error posted previously.

I appreciate the help.

[Suva1]

I was placing the parenthesis within the # signs, instead of outside.

The following works:

<cfquery name="list" datasource="#dsname#">
SELECT *
FROM students
where status = '#x#' and id in (#listqualify(select,"")#)
order by #sort_order# asc
</cfquery>

Dan, your suggestion helped, but a little more explanation would have been even more helpful. "A picture is worth a thousand words."