ListQualify and IN clause

[jgroove]

I am using CF5 and oracle 7.3.4 and I am trying to create a query with an IN clause in it. I have done this many times using the same technique but now it is causing me great pain. Here is my code:

<cfset session.Event.selTrainerID=ListQualify(session.Event.selTrainerID,&quot;'&quot;)>
<cfoutput>#session.Event.selTrainerID#</cfoutput>


<CFQUERY NAME=&quot;qTrainer&quot;>
	select FirstName,LastName
	from trainer 
	where userid in (#session.Event.selTrainerID#)
</CFQUERY>

I get the following error:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00907: missing right parenthesis


SQL = "select FirstName,LastName from trainer where userid in (''000F37C2-4888-1C6D-835B8309797D0000'',''000D7F31-3B55-1C6D-835B8309797D0000'')"


when I do an output on the selTrainerID right before I run the query, it only shows one singlequote around each value...but in the error it shows that there are two single quotes around each value. Any ideas??

 

[tlhawkins]

Have you tried putting a space in there?

<CFQUERY NAME=&quot;qTrainer&quot;>
select FirstName,LastName
from trainer
where userid in ( #session.Event.selTrainerID# )
</CFQUERY>

I know it seems simple but sometimes that causes problems, othertimes no.

 

[jgroove]

tlhawkins,

thanks for the idea, but it didn't work. Maybe you can help me out with another solution. Here is what I am trying to do. I have a select form field where you can select more than one option. I need to be able to find all of the &quot;trainers&quot; from the db based on the multiple selections. The in clause works great but doesn't work (yet). Although, in production, at most there might be 3 or 4 trainers. It must be able to deal with 1000 if they so choose. I have implemented it with looping over each id possibly executing 1000 queries, but this is not sexy nor effcient. This thing has been bothering me so much I can't think of anything else. Any ideas would be appreciated.

jgroove

 

[shammack]

I have a reply to your original question. This isn't very elegant, but it should work.

<CFQUERY NAME=&quot;qTrainer&quot;>
select FirstName,LastName
from trainer
where
<cfloop list=&quot;#session.Event.selTrainerID#&quot; item=&quot;i&quot;>
userid = '#i#' or

</cfloop>
1 = 1
</CFQUERY>

 

[tlhawkins]

Oh. OK.

A select field returns a list of all selected fields comma delimited like this : 2,5,10,8 where those are the values set by the Select.

So you should be able to output that field directly into the Query because Queries like comma delimited lists. So try:

<CFQUERY NAME=&quot;qTrainer&quot;>
select FirstName,LastName
from trainer
where userid in ( #Form.SelectFieldName# )
</CFQUERY>

That works for me everytime...
Have fun.

 

[shammack]

You may want to consider making your ID's numeric. This problem with the quotes in the IN clause when you pass it a list does not exist for lists of numeric data.

Alternatively, another option is to use a stored procedure to parse your list rather than attempting to do it with ColdFusion in the CFQuery tag.

 

[CFDude]

Have you tried using the PreserveSingleQuotes function?

e.g
where userid in ( #PreserveSingleQuotes(session.Event.selTrainerID)# )

HTH,
Tim P.

 

[Guest_imported]

Use the CF function PreserveSingleQuotes():

where userid in (#PreserveSingleQuotes(session.Event.selTrainerID)#)