2

In Coldfusion I'm trying to do a select query on an Oracle database table that I only have read access to. I have a list (lets call it myList) of several thousand IDs that I want to do the query on. 

SELECT * FROM table
WHERE userID IN (#myList#)

The issue I have is that Oracle only lets me use IN with 1000 items at a time. What is the most efficient way to approach this? Break up the list by 1000 and append the resulting queries? If so what should my code for breaking the list look like?

Thanks!

user3376065
  • 1,137
  • 1
  • 13
  • 31
  • 3
    Try using temporary table: http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause – Tadas Davidsonas May 30 '14 at 15:49
  • 3
    I would seriously reconsider what you're doing with your data if you need an `in()` clause with over 1000 entries in it. Where are these IDs coming from: the same DB? Can you not move more of the data processing back into the DB? Could you expand the detail of how you came to need to be doing this a bit? – Adam Cameron May 30 '14 at 16:49
  • @AdamCameron maybe his job bears a closer resemblance to mine than yours. – Dan Bracuk May 30 '14 at 17:50
  • I would agree with Adam. I'm not a big fan of IN to begin with, but when you're talking about IN (<1000 things>) it gets to be somewhat excessive and inefficient.Where is myList coming from? If it originates in a query, just INNER JOIN the tables on the userID. If it's generated somewhere else, try to dump it into a temp table. – Shawn May 30 '14 at 18:52
  • 1
    @DanBracuk... it's too early to say. A lot of people pull data from a DB, monkey with it in CFML and then pull more data from the DB. ANd often the CFML side of things is better done *in the DB*. CFML is (generally) about text processing, not data processing. – Adam Cameron May 30 '14 at 19:35
  • 1
    A lot of people might do that. In fact, based on questions we've seen over the years, some people do this sort of thing because they don't know how to join tables. But there are also people in situations like mine. I work in a hospital with a fair number of clinical systems that not only have different databases, but different database engines. Sometimes the job at hand involves more than one system. Even then, while ColdFusion might not be the best way to do that particular job, sometimes it's the only way available. – Dan Bracuk May 30 '14 at 19:53
  • The 2100 parameter limit in sql server is similar to, but not the same as the 1000 list in oracle. This is not a duplicate of that question. It might be the duplicate of another question, but not that one. – Dan Bracuk Jun 01 '14 at 12:16
  • I'm querying another database (MS SQL Server) to get the list of IDs. They are about 6000 IDs in total. Now I need to query the Oracle database based on this list of IDs, I'm not sure how to construct the temp table with this list of IDs. You're absolutely correct, for what ever reason my server can't handle it. Can you show me how to construct the temp-table query? – user3376065 Jun 03 '14 at 02:42

2 Answers2

2

There is a function available on cflib.org called ListSplit. It splits your long list into an array of shorter lists. Once you find and run this, your query starts to look like this:

 select JustTheFieldsYouNeed
 from SomeTables
 where 1 = 3
 <cfloop array="#ArrayOfShorterLists#" index = "ThisList">
 or SomeField in 
 (<cfqueryparam cfsqltype="cf_sql_integer" value = "#ThisList#" List = "yes"> )
 </cfloop>
Fish Below the Ice
  • 1,273
  • 13
  • 23
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
1

If you're looking to do this for ad hoc queries, this can be done with a WITH query:

WITH
UserList AS
(
SELECT 'UserID1' AS UserID FROM DUAL UNION ALL
SELECT 'UserID2' AS UserID FROM DUAL UNION ALL
...
SELECT 'UserIDX' AS UserID FROM DUAL
)
SELECT t.*
FROM table t
  JOIN UserList U
    ON t.UserID = U.UserID

That list in between the parentheses of the UserList WITH query can be as long as you want it to be, and can pretty easily be created with Excel (assuming that's where the list you want to check originated).

-

Example Excel formula (assuming your first value is in A2):

="SELECT '" & A2 & "' AS UserID FROM DUAL" & IF(A3="", "", " UNION ALL ")

or, if your UserID is numeric:

="SELECT " & A2 & " AS UserID FROM DUAL" & IF(A3="", "", " UNION ALL ")

(autofilling this down to the bottom of your list will create everything you need to paste in between the parentheses of that query above)

-

HOWEVER, note that this is only good for ad hoc type queries. This is not a good solution if you need to do this on an automatic basis. If this is for a more permanent solution, use temp (or permanent) tables.

Edit Starts Here

Here is some simple code that shows how to do this in ColdFusion

<cfset ids = "1,2">
<cfquery name="x" datasource="oraclesomething">
with IDList as (
select 0 id
from dual 
where 1 = 2
<cfloop list="#ids#" index="ThisID">
union
select #ThisID# id
from dual
</cfloop>
)
select count(*) from some_table join IDList on someid = id
</cfquery>
<cfdump var="#x#" metainfo="no">
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
troy
  • 382
  • 2
  • 8
  • Good answer. It would be interesting to see whether or not this method would outperform the one I suggested. – Dan Bracuk May 30 '14 at 17:41
  • Cool. I don't use a lot of Oracle. I didn't know you could do common table expressions in Oracle like this. A CTE would probably be the best way to go. – Shawn May 30 '14 at 18:57