title: value limitation in an IN clause Oracle
body: <p>I work for a company that has a DW - ETL setup. I need to write a query that looks for over 2500+ values in an <code>WHEN - IN</code> clause and also over 1000+ values in a <code>WHERE - IN</code> clause. Basically it would look like the following:</p>
  
  <pre><code>SELECT
      ,user_id
      ,CASE WHEN user_id IN ('user_n', +2500 user_[n+1] ) THEN 1
      ELSE 0
      ,item_id
  FROM user_table
      WHERE item_id IN ('item_n', +1000 item_[n+1] );
  </code></pre>
  
  <p>As you probably already know PL/SQL allows a maximum of 1000 values in an <code>IN</code> clause, so I tried adding <code>OR - IN</code> clauses (as suggested in other stackoverflow threads):</p>
  
  <pre><code>SELECT
      ,user_id
      ,CASE WHEN user_id IN ('user_n', +999 user_[n+1] )
       OR user_id IN ('user_n', +999 user_[n+1] )
       OR user_id IN ('user_n', +999 user_[n+1] ) THEN 1
       ELSE 0 END AS user_group
      ,item_id
  FROM user_table
      WHERE item_id IN ('item_n', +999 item_[n+1] )
      OR item_id IN ('item_n', +999 item_[n+1] );
  </code></pre>
  
  <p>NOTE: i know the math is erroneous in the examples above, but you get the point</p>
  
  <p>The problem is that queries have a maximum executing time of 120 minutes and the job is being automatically killed. So I googled what solutions I could find and it seems Temporary Tables could be the solution I'm looking for, but with all honesty none of the examples I found is clear enough on how to include the values I want in the table and also how to use this table in my original query. Not even the ORACLE documentation was of much help.</p>
  
  <p>Another potential problem is that I have limited rights and I've seen other people mention that in their companies they don't have the rights to create temporary tables.</p>
  
  <p>Some of the info I found in my research:</p>
  
  <p><a href="https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#i1006400" rel="nofollow noreferrer">ORACLE documentation</a></p>
  
  <p><a href="https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause">StackOverflow thread</a></p>
  
  <p><a href="https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit?lq=1">[StackOverflow thread 2]</a></p>
  
  <p>Another solution I found was using tuples instead, as mentioned in <a href="https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit">THIS thread</a> (which I haven't tried) because as another user mentions performance seems greatly affected.</p>
  
  <p>Any guidance on how to use a Temporary Table or if anyone has another way of dealing with this limitation would be greatly appreciated.</p>