Geek asks and answers

Cannot put more than 1000 items in IN EXpression CFQUERY

The number of elements that we can put in the expression "in" is limited to 1000 in Oracle. We have a request in our code:

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

Here # somelist # is a CF list variable.

What would be the most suitable CF solution to solve this problem?

+3
source share
7 answers

This will probably be a driver limitation, but, of course, if you need to pass 1000 elements in the inline query list, I would say that your architecture is incorrect somewhere along the line.

Well, if you could tell us why you need to do such a thing and what you are trying to accomplish, as I may be able to help you further.

( ), , - :

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

500, .

UPDATE: cfqueryparam, SQL-

+9

. temp :

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

. SO: 1000 Oracle IN.

, . (SQL-). CF, , bind.

+9

. listLen(someList) . , 1000 "IN". , .

+1

:

  • , , Vincent
  • 1000, , CF UNION
  • temp DB, # 1
  • cfloop OR ID = #i# .
  • , IN, , .
+1

, DML , oracle, TABLE() SQL, .

. )

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;
0

... , , , , ... - :

<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>
0

SPLIT UDF,

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

, IN

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

, split .

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

CREATE FUNCTION dbo.Split (@RowData nvarchar (2000), @SplitOn nvarchar (5))
RETURNS table @RtnValue (Id int identity (1,1), Data nvarchar (100)) AS
START 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

-1
source

More articles:


All Articles