Current Position:Home > Using in clause in oracle

Using in clause in oracle

Update:11-30Source: network consolidation
Advertisement
Hi,
I am facing the following issue when trying to retrieve the data from a view.If i specify literals inside the in clause (example id's inside in clause) it's working fine,but when i try to use the subquery inside in clause to retrieve the id's from the base table it's taking longer time and no results are observed.I am trying to give the subquery inside in clause due to the in clause cannot handle literals(id's) more than 1000.
Any suggestions on this will help me a lot for this.

The Best Answer

Advertisement
user13469868 wrote:
... due to the in clause cannot handle literals(id's) more than 1000.
Any suggestions on this will help me a lot for this.I often do a simple approach. If I need an IN list with more then 1000 values, then simply use two more more in lists and the OR operator.
Example: instead of
select * from emp
where id in
( 1,
  2,
  3,
  1000,  /* problem point */
  1001,
  2000,  /* problem point */
  2001,
)I write the statement like this:
select * from emp
where (id in  /* see the addition starting parenthesis! */
( 1,
  2,
  3,
   999)
OR id in  /* start of the second list */
  (1000,
  1001,
   1999)
OR id in   /* start of the next list */
(2000,
  2001,
) /* remember to add the closing parenthesis */Edited by: Sven W. on Jun 28, 2011 4:39 PM - formatting
  • Unable to use the with clause in oracle 9.0.2 Update:10-11

    Hi, I need to use oracle SQL with clause in oracle 9.0.2 database. Its a 9i feature but i am unable to use it. It is giving internal error, when i try to execute it. Even for simple query: WITH acct_summary as ( select TOT_COLL_AMT from tdc_acct) sel

  • Bug in WITH clause (subquery factoring clause) in Oracle 11? Update:10-11

    I'm using WITH to perform a set comparison in order to qualify a given query as correct or incorrect regarding an existing solution. However, the query does not give the expected result - an empty set - when comparing the solution to itself in Oracle

  • What is equivalent to 'with clause' in oracle Update:11-30

    what is equivalent to 'with clause' in oracle thanksMy reqirement is : I have a table named egroup. I have to find the name of the egroup different from "other" that can be found in the most countries in Africa create table EGROUP(      c_id    

  • Can't use ";" in sql clause with Oracle 8.X Update:11-30

    Can't use ";" in sql clause with Oracle 8.X I can't use ";" at the ending of sql clause in VB program. First this program can use with Oracle 7.3.4 database. But now i need to upgrade DB to Oracle 8.1.7 ,program can't operate. It show

  • How to avoid repeat where clause in oracle sql Update:10-11

    Hi, Please find my query below, I need a help to avoid duplication of **where** clause in my query. In my below query, **JOIN** condition is same for both the queries and **WHERE** condition also same except this clause "and code.code_name="tran

  • Clarification on using function in where clause of oracle sql query Update:10-11

    I have an issue in regarding function using where clause of sql query.. We are facing performance issue while executing query, so in what ways to improve the performance of the query which i have posted below. select col ,case when my_function(parame

  • Order By clause in Oracle Update:10-11

    Hello, Sorry I am posting it for the second time In Oracle , the order by clause does not return the expected query result, if any of the field value in the order by clause has an empty string. Oracle treats the empty string as null value and ORDER B

  • Group By clause in oracle 10g help needed Update:11-30

    Hi we have a requirement that get the AR aging details at customer level.I have written the following query to fetch the correct rows at invoice level.But now i need to sum the amounts and i should show at invoice level and customer level.Could you p

  • Curious about this From clause in Oracle XE Update:11-30

    SQL view of query Builder gives the following: select     "EMPLOYEES"."EMPLOYEE_ID" as "EMPLOYEE_ID",      "EMPLOYEES"."FIRST_NAME" as "FIRST_NAME",      "EMPLOYEES"."LAST_NAME&quo

  • Using OR operator in the WHERE clause in Oracle BI Update:11-30

    Hi, i am using Oracle BI EE 10.1.3.3.3. I construct the simple report in BI Answers on the Accounts presentation layer, and use the following filter clause: "WHERE (AccountNum BETWEEN '441' and '473') OR (БалСчет1Порядка BETWEEN '501' and '519')"