Help with Array Function Delimiters 1

[iao]

I am trying to run an SQL query using an ArraytoList function, and I can't get the delimiters to set correctly. Let me explain...

I have a form where the users enter in their selections and submit the page. On the next page, the selected items are in an array. I want to run an SQL query based on what items were selected from the previous page. So, to run the query, I want to set my array to a list.

<CFSET List = ArrayToList(myArrayList, &quot;''&quot;)>

<CFQUERY NAME=&quot;qStPick&quot; DBTYPE=&quot;Oracle80&quot;>
SELECT ID
FROM NAMES.ID
WHERE ID IN ('#List#')
</CFQUERY>

The problem I am having lies in how the delimiter is set. SQL needs to have the ID's set up like: '1', '2', '3', etc. I can't set my delimeter to disply the ID as required. Here are the delimeters I have set and what the results are:

ArrayToList(myArrayList, "''&quot: '1''''2'
ArrayToList(myArrayList, "'&quot: '1''2'
ArrayToList(myArrayList, ",'&quot: '1,''2'
ArrayToList(myArrayList, "',&quot: '1'',2'
ArrayToList(myArrayList, "','&quot: '1'',''2'

Any ideas on how to set my delimiter so that the output is like: '1','2'? Any help is much appreciated. Thanks!

 

[strantheman]

hi iao

Do not use quotes. Since the field you're filtering on is an integer your list of IDs do not need quotes.

<CFSET List = ArrayToList(myArrayList, &quot;,&quot>:
should give you 1,2

... then your SQL will look like:

<CFQUERY NAME=&quot;qStPick&quot; DBTYPE=&quot;Oracle80&quot;>
SELECT ID
FROM NAMES.ID
WHERE ID IN (#list#)
</CFQUERY>

 

[iao]

Worked like a charm. Thanks for the help!