Using a List within a Where Clause

Forum|Forum|20 years ago
April 26, 2006
7 replies
723 views

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>

Advanced techniques

This topic is closed to new replies. Start a new post to keep the conversation going.

Correct answer Dan_Bracuk

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.

S

Suva1Author

Participating Frequently

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.

S

Suva1Author

Participating Frequently

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.

S

Suva1Author

Participating Frequently

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."

D

Dan_Bracuk

Inspiring

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

D

Dan_BracukCorrect answer

Inspiring

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.

S

Suva1Author

Participating Frequently

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?

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.