Cannot put more than 1000 elements in a IN Expression CFQUERY

The number of elements that we can put in a "in" expression is limited to 100开发者_StackOverflow0 in Oracle. We have a query in our code:

<cfquery>
SELECT * from table1 where ID IN (#somelist#)
</cfquery>

Here #somelist# is CF list variable.

What would be the most appropriate solution in CF to solve this issue?

---

Use a temporary table. Insert Ids into temp, and query:

SELECT * from table1 where ID IN (SELECT id FROM temp_table)

See this question on SO: How to put more than 1000 values into an Oracle IN clause.

Also it looks like you're doing a simple substitution. This is a bad idea from a performance point of view, and a really bad idea from a security point of view (SQL injection). I don't know CF, but I'm pretty sure you can use bind variables.

---

This will probably be a driver limitation, but surely if you need to pass 1000 items in a list to an inline query, I'd say your architecture is flawed somewhere along the line.

It be good if you could tell us why you need to do such thing, and what you're trying to accomplish, as I might be able to help you furthermore.

In principle (if that query is inevitable), I'd say you could simply do something like:

SELECT * from table1 where ID IN (<cfqueryPARAM value = "#somelist#" CFSQLType = "CF_SQL_VARCHAR">) OR ID IN (<cfqueryPARAM value = "#somelist2#" CFSQLType = "CF_SQL_VARCHAR">) ...

And you could break it down in chunks of 500 for example.

UPDATE: Added cfqueryparam to avoid SQL injection

---

You should check the size of the list prior to executing the query. You can use listLen(someList) to return the length. I would also echo the other responses here and suggest you investigate why you might have more than 1000 items in the "IN" list. Perhaps you could use a subquery instead.

---

You can:

1. Use a subquery like Vincent describes
2. break the list into chunks of a size less than 1000, do a query for each chunk then join the queries afterward with a CF query on queries and using UNION
3. insert the list in to a temp DB table then do #1
4. use cfloop to loop thru the list and write a OR ID = #i# in to the query for each item in your list.
5. Probably the best solution is to avoid using the IN operator unless you are sure that the list length will always be less than your limit.

---

Another option rather than doing the DML to load a temporary table, define an oracle collection type, populate it with your list, and then use the TABLE() function in your SQL to join on it.

e.g. for a collection of numeric IDs)

CREATE OR REPLACE TYPE GENERIC_ID as object (id_value number)
/

CREATE OR REPLACE TYPE GENERIC_ID_LIST as TABLE of GENERIC_ID
/

-- create a variable of that type, load it up and then

SELECT t1.* from table1 t1, table(your_generic_id_list) c1
where t1.ID = c1.id_value; 

---

Just resize the list... if at all possible, don't use a HUGE list, just use a sub-query, but if you've got to use a list that large... do something like this:

<cffunction name="createInArray" returntype="array" output="false">
    <cfargument name="sList" type="string" required="true" />

    <cfset var aOut = ArrayNew(1) />
    <cfset var iLL = ListLen(arguments.sList) />
    <cfset var bContinue = true />
    <cfset var i = 1 />
    <cfset var x = "" />

    <cfloop condition="bContinue">
        <cfif ListLen(arguments.sList) GT 500>
            <cfset x = ListGetAt(arguments.sList,501) />
            <cfset x = Find(",#x#", arguments.sList) />
            <cfset aOut[i] = Mid(arguments.sList,1,x) />
            <cfset arguments.sList = Replace(arguments.sList,aOut[i],"") />
        <cfelse>
            <cfset aOut[i] = arguments.sList />
            <cfset bContinue = false />
        </cfif>
        <cfset i = i+1 />
    </cfloop>

    <cfreturn aOut />

</cffunction>

<cfset somelist = createInArray(somelist) />

<cfquery>
    SELECT * 
    FROM table1
    WHERE <cfloop from="1" to="#somelist.length()#" index="i"><cfif i NEQ 1>OR</cfif> ID IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#somelist[i]#" list="true" separator=",">) </cfloop>
</cfquery>

---

I use the SPLIT function in UDF to do stuff like this all the time

Select Item from dbo.fnSplit('1,2,3,4,5,6,7,8,9', ',')

This means that you don't even need to USE the IN clause

Select * from Employees CROSS APPLY dbo.FnSplit('1,2,3,4,5,6,7,8,9', ',') Where ManagerID = Item

I reccomend doing some testing on which split function is best for your needs

ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces ) GO

or

CREATE FUNCTION dbo.Split ( @RowData nvarchar(2000), @SplitOn nvarchar(5) )
RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100) ) AS
BEGIN Declare @Cnt int Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
    Insert Into @RtnValue (data)
    Select 
        Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

    Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return

END

etc, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648